Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
C_H
Advocate I
Advocate I

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

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

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

 

Capture.PNG

 

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

4 REPLIES 4
LivioLanzo
Solution Sage
Solution Sage

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

 

Capture.PNG

 

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

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.

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 🙂

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

@LivioLanzo

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

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.