Help with Union DAX

[rbreneman]

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?...

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

[Greg_Deckler · Accepted solution]

@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

[rbreneman]

@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.