Subselect result only with measures

[C_H]

Hi,

I have follwing sample data in a table:

Client  Year    Revenue

4711    2017    100

4711    2018    200

4712    2017    101

4712    2018    201

4713    2018    202

4714    2018    203

4715    2017    102

4715    2018    204

Is there a way to display in a table visual:

- only the Clients which had Revenue in 2017

- for these Clients all the Years with the corresponding Revenue

Client  Year    Revenue

4711    2017    100

4711    2018    200

4712    2017    101

4712    2018    201

4715    2017    102

4715    2018    204

I want to achieve it without modelling (calculated columns/tables) but only with measures and visual filters.

Thx in advance

[LivioLanzo · Accepted solution]

Hello @C_H,

you can build the below model and add this measure:

Measure = 
CALCULATE(
    SUM( Revenue[Revenue] ),
    CALCULATETABLE(
        VALUES( Clients[Client] ),
        Years[Year] = 2017,
        CROSSFILTER( Revenue[Client], Clients[Client], Both )
    )
)

[C_H]

Thanks 🙂 

Would there also be a workaround without remodeling to 2 Dim/1 Fact-Table?

We often have questions in this direction from "Power Users" who come from other analytic tools where they can do this within an ad-hoc Query editor on an semantic layer (SQL like).

In our approach we want them to build reports on given datasets where they don't have the abilitiy to change the queries/model underneath but work with measures within PBI Desktop to fullfill their flexibility needs.

[LivioLanzo]

Hi @C_H!

I highly advise to adopt the Dimensional Modelling approach when using Power BI. 

I understand that at the beginning it can be a bit tricky for people coming from an OLTP background but that is how Tabular models work best. Besides, for data savvy people such as your power users it should not be rocket science to grasp it 🙂

[C_H]

@LivioLanzo

BTW: it works also with only one table. Thanks a lot 

Measure =
CALCULATE (
    SUM ( test_table[Revenue] ),
    CALCULATETABLE ( VALUES ( test_table[Client] ), test_table[Year] = 2017 )
)