Hi All,

Error message  - An invalid numeric representation of a date value was encountered

Root cause - We are trying to compare with a measure value

Workaround - The workaround suggested by the documentation is to filter the Date table and pass it as a table filter to calculate. This requires ignoring first the filter context on the Date table, only to overwrite it later with the ‘as of date’.

Solution - =CALCULATE( SUM(Sales[SalesAmount]), FILTER( ALL( ‘Date’[CalendarYear]), [CalendarYear] = MAX(‘Date’[CalendarYear]) ) )

Refer below link for solution

https://prologika.com/dax-variables-to-rescue/

To create Date Dimension in SSAS Tabular model

 Hi All,

To create date dimension in SSAS tabular model

Click on calculated table and then paste the below in the formula

=CALENDAR ("1/1/2015","1/1/2030")

And then give the date table your name and follow the below steps to make it effective

In Visual Studio, in the model designer, click the date table.

Click Extensions > Table > Date > Mark as Date Table.

In the Mark as Date Table dialog box, in the Date listbox, select a column to be used as a unique identifier. This column must contain unique values and should be of Date data type.

If necessary, create any relationships between fact tables and the date table.

Create many to many relationship in Tabular model

Hi All,

The below saved my time today. Many to Many is supported with SQL server compatibility level 1500 or more.My development server did not have this compatibility

This is one of tip for BI Architect. Take all latest versions of tools so that we will never get any compatibility issues and we will be able to use all best features available. 

Anyways below link helped me achieve my results. 

 https://www.google.com/search?safe=active&rlz=1C1GCEB_enIN896IN897&sxsrf=ALeKk00cv_-3yUUH3JuF4vvNzJVcgGsOqA%3A1600131070409&ei=_g9gX-XKGKvrz7sP_oOy2AM&q=create+many+to+many+relationship+power+bi&oq=create+many+to+many+&gs_lcp=CgZwc3ktYWIQAxgFMgIIADICCAAyAggAMgIIADICCAAyBwgAEBQQhwIyAggAMgIIADICCAAyAggAOgQIABBHOgUIABCRAjoECAAQQzoICC4QxwEQowI6BAgjECc6CwguELEDEMcBEKMCOgoIABCxAxCDARBDOgUIABCxA1DnsJYEWLPQlgRgmOiWBGgAcAJ4AIAB6gGIAegXkgEGMC4xNi40mAEAoAEBqgEHZ3dzLXdpesgBCMABAQ&sclient=psy-ab#kpvalbx=_OjJgX9_yCvvhz7sP-cSjiAU39

Thank you so much God for being with me and helping me

To obtain selected value from Date Slicer in Power BI

 Hi All,

Well I did not spend much time to find answer to this valuable question

Resolution - CAL_DT_VALID_TO =

CALCULATE (MAX (D_SALES_STATE_HIST[DT_DOCUMENT]), ALLSELECTED (D_SALES_STATE_HIST[DT_DOCUMENT] ) )

Enjoy coding !!

SWITCH STATEMENT IN DAX

 Hi Friends!

Spent the whole day in understanding how to implement the switch statement in Direct query , tabular model. 

I finally found workaround for it. Include the switch statement in filter columns. I have used EXCEPT to exclude the rows that I don't need

CALCULATE(DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT]),

EXCEPT

(

FILTER(

SUMMARIZE(D_SALES_STATE_HIST,

D_SALES_STATE_HIST[CD_SYSCOUNTRY],

D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE],

D_SF_VIP_ACCOUNT[CD_NATIONALITY],

"VT", DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT])),

(D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "CHN") ),

FILTER(

SUMMARIZE(D_SALES_STATE_HIST,

D_SALES_STATE_HIST[CD_SYSCOUNTRY],

D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE],

D_SF_VIP_ACCOUNT[CD_NATIONALITY],

"VT", DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT])),

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "AUS" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "NZL") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "AUS" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "AUS") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "THD" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "THA") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "KLM" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "KOR") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "MAC") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "HKG") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_NATIONALITY] = "MAC") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_NATIONALITY] = "HKG") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE]) ||

ISBLANK(D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE])

)

)

Be Happy !

The column '...' specified in the 'SUMMARIZE' function was not found in the input table.

 Hi All,

To resolve above error 

1. check if you are connected and using the right Analysis tabular model.

2. Also check the syntax of the query, you might be missing a comma

3. If you are using the Summarize function then include the missing table as the first table name

Enjoy!

The expression refers to multiple columns, Multiple columns cannot be converted to a scalar value!

 Hi All,

How will you feel when you have spent 24 hours on finding a solution for an issue and suddenly a fluke code work for you ? Happy..? Thank God ? What else..? 

Well for me its excitement!

Fix for the above issue is as below

Enjoy Life!

The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String.

 Hi All,

So we try to read on what COUNT, COUNTX, COUNTA and all the other types of COUNT and we go nuts trying to figure out its difference

Finally I was able to understand the difference between COUNT and COUNTA

COUNT - would count int and date type columns only

COUNTA - would count text values. 

So if you encounter the error listed in the header then use COUNTA instead of COUNT

Enjoy coding !!

DAX - Get the date difference in days from the slicer in Power BI

Hi All, 

 To get the date difference in days from the slicer in Power BI, follow below code

DAYSSELECTED =

VAR FirstDay = CALCULATE(

MIN('D_SALES_STATE_HIST'[DT_DOCUMENT]),

ALLSELECTED('D_SALES_STATE_HIST'[DT_DOCUMENT])

)

VAR LastDay = CALCULATE(

MAX('D_SALES_STATE_HIST'[DT_DOCUMENT]),

ALLSELECTED('D_SALES_STATE_HIST'[DT_DOCUMENT])

)

RETURN

DATEDIFF (FirstDay,LastDay,DAY)

Create relationships for more than 1 column in SSAS tabular mode

 Hi All,

To Create relationships for more than 1 column in SSAS tabular mode was difficult as there is no provision for this in the Data model view. To achieve this, I had to create a calculated column and then create relationships. 

Follow the below link to understand how to calculate the calculated column 

https://www.sqlshack.com/creating-first-ssas-tabular-model-database/