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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rbreneman
Helper II
Helper II

Help with Union DAX

Hi!

 

I'm trying to create a calculated table that consists of some static text and measures. The measures show the correct values when I put them into a card, however when I'm creating a calculated table using UNION and ROW the measures no longer show the correct values. Screenshot below shows the card w/measures (correct values) and the arrows point to the values that the table shows (incorrect values).

 

I believe my problem is that the filters aren't coming across when using the UNION or ROW functions, but I'm not sure how to overcome this. I'm willing to try other ways of doing this but the end result visual must be a table with those 5 names and eventually more measures but I haven't created them yet. Matrix won't work for this project, everything has to be visible in that single table visual with no scrolling or clicking to expand.

 

Here is a link to my pbix if that helps: https://candoristech.sharepoint.com/:u:/s/Datacenter/ERkDh_mtqfNAs6BVLfSznlgBDPyi49AroHEFpbxGxpAKfA?...

 

rbreneman_0-1628010582241.png

Here is the DAX for the table:

ELA = 
UNION(
    ROW(
        "Name","Building",
        "Total Tested",[Building Total Tested],
        "SortOrder",1
    ),
    ROW(
        "Name","District",
        "Total Tested",[District Total Tested],
        "SortOrder",2
    ),
    ROW(
        "Name","BOCES",
        "Total Tested",[BOCES Total Tested],
        "SortOrder",3
    ),
    ROW(
        "Name","SCRIC",
        "Total Tested",[SCRIC Total Tested],
        "SortOrder",4
    ),
    ROW(
        "Name","State",
        "Total Tested",[State Total Tested],
        "SortOrder",5
    )
)

Here is the DAX for the Building measure, and the others are similar, just slightly different filters:

Building Total Tested = 
VAR School = FIRSTNONBLANK(Schools[ENTITY_CD],1)
VAR District = FIRSTNONBLANK(Districts[DISTRICT_AGG_ID],1)
VAR Subject = FIRSTNONBLANK('ELA & MATH'[ITEM_SUBJECT_AREA],1)
VAR SelectedYear = FIRSTNONBLANK('ELA & MATH'[YEAR],1)
VAR Grade = FIRSTNONBLANK('ELA & MATH'[Grade],1)
VAR TotalTested = CALCULATE(SUM('ELA & MATH'[TOTAL_TESTED]),
    ALL('ELA & MATH'),
    'ELA & MATH'[SUBGROUP_NAME] = "All Students",
    'ELA & MATH'[BEDSCODE] = School,
    'ELA & MATH'[DistrictCode] = District,
    'ELA & MATH'[ITEM_SUBJECT_AREA] = Subject,
    'ELA & MATH'[Type] = "School",
    'ELA & MATH'[YEAR] = SelectedYear,
    'ELA & MATH'[Grade] = Grade
)
RETURN
TotalTested

 

Thanks in advance for any help the community can provide!

Ryan

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@rbreneman Solution attached below sig. First, the reason it wasn't working is that your calculated table was not taking into account the slicer selections (row context instead of filter context because it is a table). So, the solution is essentially the disconnected table trick:

  1. Create a disconnected table with your values, Building, District, etc.
  2. Create a Measure2Show that depending upon the MAX of your disconnected table, return the appropriate measure

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@rbreneman Solution attached below sig. First, the reason it wasn't working is that your calculated table was not taking into account the slicer selections (row context instead of filter context because it is a table). So, the solution is essentially the disconnected table trick:

  1. Create a disconnected table with your values, Building, District, etc.
  2. Create a Measure2Show that depending upon the MAX of your disconnected table, return the appropriate measure

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler you are a rockstar! Thank you so much! Your solution works great, and I like it even more than having to manage a calculated table.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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