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
sukram1
Frequent Visitor

Table as Filter in CALCULATE

Hello,
first of all: the introduction takes a little longer, because I have to explain why we do what.
We need to create a report at our company that needs to show 3 values each for multiple distribution channels and multiple segments and products. There are 106 visuals in total. Each of these visuals is a multirow (some contain 2 values, some only 1 value). The visuals have different conditional formatting behind them, hence the 2 to 1 split.

Here is a short rough representation of how something like this looks. Each of the blue boxes is 1 visual:

example.png 

 

 

 

 

 


This representation repeats for all distribution channels.

 

The idea was that in each visual individually the filters are set. But I want to avoid setting the filters manually in 106 visuals. My idea was to create a measure for the metrics, which I can reuse:

Sum = SUM(Table[sum])
Ratio 1 = DIVIDE(SUM(Table[sum2]);SUM(Table[sum3]))
Ratio 2 = DIVIDE(SUM(Table[sum4]);SUM(Table[sum3]))

This is the structure of the measure I would create for all combinations:

Distribution_Channel_1_Total = 
CALCULATE(
    [Sum];
    Table[distribution_channel] = "A";
    Table[segment] = "Segment A";
    Table[product] = "Product A"))

My question is, is it possible to use a table in Calculation as a filter? A table that contains all necessary attributes and via an ID these attributes are taken into account in the Calculate function.
example2.png

Distribution_Channel_1_Total = 
CALCULATE(
    [Sum];
    table_with_values = 1)

 

I hope I was able to explain my request reasonably clearly.

Thank you very much in advance!

Markus

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You could try something like

Distribution channel 1 total =
VAR FilterTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Filter table',
            'Filter table'[distribution channel],
            'Filter table'[segment],
            'Filter table'[product]
        ),
        'Filter table'[id] = 1
    )
VAR Result =
    CALCULATE (
        [Sum],
        TREATAS (
            FilterTable,
            'Table'[distribution channel],
            'Table'[segment],
            'Table'[product]
        )
    )
RETURN
    Result

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

You could try something like

Distribution channel 1 total =
VAR FilterTable =
    CALCULATETABLE (
        SUMMARIZE (
            'Filter table',
            'Filter table'[distribution channel],
            'Filter table'[segment],
            'Filter table'[product]
        ),
        'Filter table'[id] = 1
    )
VAR Result =
    CALCULATE (
        [Sum],
        TREATAS (
            FilterTable,
            'Table'[distribution channel],
            'Table'[segment],
            'Table'[product]
        )
    )
RETURN
    Result

Hi @johnt75 ,

great, thank you very much! This is exactly what I was looking for!

Thanks for your help!

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.

Top Solution Authors