Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ) ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 ) ) )
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!
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |