Slicing measures in calculated table

[jscottNRG]

I'm having trouble with carrying over sliced measures in a calculated table. I believe I need to use a calculated table in order to reorganize my measures in a way that makes sense, i.e. not a single row table, which is what I get when I drop those measures into a table visual. I have a table of measures called 'My Calcs' that I'm using to organize measures from various other tables.

If I build a table using just the measures from 'My Calcs', I can apply slicers. But If I try to reorganize the measures using a calculated table with DAX like below, the measures in the calculated table are no longer sensitive to the slicers.

Inspection Item Results = 
UNION(
ROW("Inspection Item", "Item 1", "Pass Count", 'My Calcs'[Item 1 Pass], "Fail Count", 'My Calcs'[Item 1 Fail]),
ROW("Inspection Item", "Item 2", "Pass Count", 'My Calcs'[Item 2 Pass], "Fail Count", 'My Calcs'[Item 2 Fail]))

Here's the way I'd like the reorganized (calculated) table to look, but I can't figure out how to maintain the slicer effect once the measures are passed to this new table:

Any thoughts or guidance? Please let me know if more clarification is needed. I'm certainly open to any other method for reorganizing the measures in the way I've depicted, but I'm new to PBI and DAX and this is the only means I've found so far.

Thanks!

[v-ljerr-msft · Accepted solution]

Hi @jscottNRG,

According to the description above, you should be able to follow steps below to reorganize the measures on a Table visual, instead of using a calculated table.

I assume you have table with a column called "Inspection Item" of value "Item 1", "Item 2"(if you don't have it yet, you need to add it first).

1. Use the formula below to create a new measure for "Pass Count".

Pass Count =
SWITCH (
    FIRSTNONBLANK ( 'Table1'[Inspection Item], 1 ),
    "Item 1", 'My Calcs'[Item 1 Pass],
    "Item 2", 'My Calcs'[Item 2 Pass],
    0
)

2. Use the formula below to create a new measure for "Fail Count".

Fail Count =
SWITCH (
    FIRSTNONBLANK ( 'Table1'[Inspection Item], 1 ),
    "Item 1", 'My Calcs'[Item 1 Fail],
    "Item 2", 'My Calcs'[Item 2 Fail],
    0
)

Note: Just replace "Table1" with your real table name that has the "Inspection Item" column.

3. Then you should be able to show the "Inspection Item" column with the new "Pass Count" and "Fail Count" measure on the Table visual. 

Regards

[jscottNRG]

Fantastic!  Thanks v-ljerr-msft - I have over 200 "Inspection Items" so it wasn't exactly fast to implement this solution, but it worked exactly as intended and got me past my sticking point.

I appreciate the help!

[Anonymous]

Realize that a calculated table is only "refreshed" when all your data is refreshed.  It isn't really "dynamic" and should be thought of just like any other table that you (say) pulled from SQL or a CSV.  It's... just a table (that happen to have gotten data via a DAX query, but that is irrelevant while lookin' and slicin' your report).

You can certainly create relationships w/ calculated tables, and slicers should work against the calculated table... but not "during refresh"... if you get me?

[jscottNRG]

Many thanks for your reply scottsen -- that makes sense, and I suspected that the issue was something like what you describe.  Do you have a suggestion for reorganizing the measures in the way I've depicted?  I'd describe the measures as summary data, so by design I'm losing the observation-level identifiers (i.e. master key), which makes establishing a relationship between the calculated table and the source tables tricky, at least given my limited experience level!

[Anonymous]

Would probably need to see more of your model (tables, relationships, etc) to provide further insight. 

Maybe... avoid the calculated table? 🙂