Hi All,
Learnt few date functions in DAX
1 - To obtain Week Num = =WeekNum(DateColumn, 2)
2 - if you want day to start from Monday and 1 - if you want Sunday
2 - To obtain Month name
=Format(DateColumn,"MMM")
3 - To obtain Day name
=Format(DateColumn,"dddd")
Hi All,
Error while creating relationship between two tables in SSAS tabular model - RELATIONSHIP IS many to many relationship which is currently not supported.
To resolve this, create a bridge table and then establish One to many and Many to One relationship between the tables.
You may use the below DAX query to create Distinct value
=DISTINCT(D_EXCHANGERATE[CD_FROMCUR])
Enjoy coding!
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/
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.
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
Hi All,
Well I did not spend much time to find answer to this valuable question
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 !
