Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
SarikaKumari18
Helper III
Helper III

Urgent Help Needed with DAX query in Power BI

Hello all,
I have fact table "Test" and one disconnected table "Language".
Language table has contry and languagecode column where Belgium has two languagecode . we have RLS applied on this so that customer sees only their country language type in slicer .

So if customer selects languageType "nl-be" then it should filter responses for both language code "nl-be" and "en".
Similarly, on selection of "fr-be", data should filter for "fr-be" and "en".

Below is the sample data of Fact table

SarikaKumari18_2-1645807021440.png


I have written below DAX measure to achieve this which works fine on latest data where we added translation for response .

ResponseTranslation =
SWITCH(SELECTEDVALUE('Language'[LanguageType]),
"nl-be",MAXX(FILTER(Test,Test[languagecode] IN {"nl-be","en"}),Test[response]),
"fr-be",MAXX(FILTER(Test,Test[languagecode] IN {"fr-be","en"}),Test[response]),
MAX(Test[response]))

The problem is with historical data where we don't have translation available and hence the languagecode either blank or doesn't match with latest languagecode in fact table so they see all responses data only if they don't select any LanguageType.
 
Requirement :
For old data, they want to see all responses (whatever is there in system) data irrespective selection of LanguageType but response column can't be blank in table on selection of fr-be or nl-be.
However, It shows data when you don't select any languagetype for historical records but they want to select one of the lanuage type always and it can't be blank . (Language Type might be replaced with radio button in future)

This is for recent data result which is fine (I have used DAX measure in table insetad of response column)
 
SarikaKumari18_0-1645806846471.png

 

 For old data it should show everything on selection of any language type

SarikaKumari18_1-1645806846463.png

I am looking forward to hear from you all. Please help me with this requirement / share idea how to achieve this please
Thanks in advance!

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

What if you inverted the logic and told it what not to include?

 

SWITCH (
    SELECTEDVALUE ( 'Language'[LanguageType] ),
    "nl-be", CALCULATE ( MAX ( Test[response] ), Test[languagecode] <> "fr-be" ),
    "fr-be", CALCULATE ( MAX ( Test[response] ), Test[languagecode] <> "nl-be" ),
    MAX ( Test[response] )
)

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

What if you inverted the logic and told it what not to include?

 

SWITCH (
    SELECTEDVALUE ( 'Language'[LanguageType] ),
    "nl-be", CALCULATE ( MAX ( Test[response] ), Test[languagecode] <> "fr-be" ),
    "fr-be", CALCULATE ( MAX ( Test[response] ), Test[languagecode] <> "nl-be" ),
    MAX ( Test[response] )
)

@AlexisOlson Great ! Thanks very much it's working abosultely fine with test data .
I will try in my real power bi dataset. 
Thanks a lot for solution . Very much appreciated !

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors