Forum Discussion

EACruz's avatar
EACruz
Frequent Visitor
1 year ago

Creating a Dimension that is dependent on Parameter Selected

Hello,
I am transitioning from Tableau to Power BI and am Struggling finding DAX Expressions that allow the rows/columns in a Matrix to adjust based on the parameter selection. In Tableau this was a simple task because I could just write the calculated field below.

Aging Days

if [Aging Parameter] = "DOS Aging" Then [DOS Aging]

elseif [Aging Parameter] = "Denial Aging" then [Denial Aging]

END

Aging Group

CASE [Aging Days]

WHEN "000 - 030" THEN "000 - 090"

WHEN "031 - 060" THEN "000 - 090"

WHEN "061 - 090" THEN "000 - 090"

ELSE "091 +"

END

 

How can I create a new Column that groups the days based on the Parameter Selection in a Matrix? No Matter which Aging Parameter is selected the Grouping is the same but the Aging Days will vary based on which selection is made (DOS or Denials).

Thank You in advance for your help!

 

 

 

Parameters switch

3 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi, EACruz ,
    Here's how to achieve dynamic aging groups in Power BI using DAX:

    1. Create a Parameter Table (Disconnected Table) : AgingParameter = DATATABLE("Aging Selection", STRING, { "DOS Aging", "Denial Aging" })

    2. Capture Selected Parameter:
      SelectedAging = SELECTEDVALUE(AgingParameter[Aging Selection], "DOS Aging")
    3. Define Aging Days Based on Selection:
      AgingDays = SWITCH([SelectedAging], "DOS Aging", SELECTEDVALUE('YourTable'[DOS Aging]), "Denial Aging", SELECTEDVALUE('YourTable'[Denial Aging]))

    4. Create Aging Group Logic: AgingGroup:
      AgingGroup = SWITCH(TRUE(), [AgingDays] IN {"000 - 030", "031 - 060", "061 - 090"}, "000 - 090","091 +")

    5. Use in Matrix Visual

      • Place AgingGroup in Rows.
      • Use measure values in Values.
      • Add AgingParameter[Aging Selection] as a slicer.

     

     

     

    • EACruz's avatar
      EACruz
      Frequent Visitor
      1 year ago

      Hi rohit1991 ,
      I appreciate the detailed explanation! I was able to reacreate all of the Fields as you mentioned above.

      The only challenge with 'Aging Days' is that if we use the SELECTEDVALUE function then we are expecting to see One Distinct Value but as shown below 'DOS Aging' and 'Denial Aging' has a list of Values. I wanted to use it as a Dimension to toggle between one and the other without the addition of another Parameter. In other words, 'Aging Selection' should allow me to switch between 'DOS Aging' and 'Denial Aging' Dimensions.