Forum Discussion

C_H's avatar
C_H
Advocate I
7 years ago
Solved

Subselect result only with measures

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

Tips and Tricks
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    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 )
    )
)

     

     

4 Replies

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    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's avatar
      C_H
      Advocate I
      7 years ago

      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's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        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 :)