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.
Hello,
I'm struggling with a problem where i have a excel connected table something like this
Name | Date | Score |
User1 | 01-Jan-17 | 10 |
User2 | 01-Jan-17 | 11 |
User3 | 01-Jan-17 | 12 |
User4 | 01-Jan-17 | 13 |
User5 | 01-Jan-17 | 10 |
User1 | 02-Jan-17 | 2111 |
User1 | 02-Jan-17 | 233 |
User1 | 04-Jan-17 | 1733 |
User2 | 02-Jan-17 | 1920 |
Now, i have derived a DAX table which provides me with the SUM of the 'Score', grouped by the 'Name'. Something like this
User1 | 4087 |
User5 | 10 |
User3 | 12 |
User2 | 1931 |
User4 | 13 |
I would like this DAX table to update if i apply a visual filter of DATE on the first table. i.e. if the first table is filtered to only include 1-Jan-2017 and 2-Jan-2017, then the derived DAX table should change to
User1 | 2354 |
User5 | 10 |
User2 | 12 |
User2 | 1931 |
User4 | 13 |
Is this possible?
Solved! Go to Solution.
@abhirajkakani wrote:
Hi Sumit,
Thanks for the reply. The real problem which i'm trying to solve cannot be solved using a measure. It requires calculated columns and hence it is very necessary for me to get a derived DAX table.
I cannot share the real problem for the obvious reasons but measures cannot be used for the operations that i need to perform on the table.
Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.
Measure actually may work, I think. You could declare calculated table variable and calculate against the variable, the variable would repsonse to the slicers.
Measure = VAR tempTable = CALCULATETABLE ( SUMMARIZE ( 'table', 'table'[Name], "TOTAL", SUM ( 'table'[Score] ) ) ) RETURN COUNTROWS ( tempTable )
Hi @abhirajkakani,
I dont think you need a seperate table to get roll up numbers at user level, you can just create a measure using ALLEXCEPT and put date column as exception, this will result in user level rolled up numbers and all the filters of you table will work on the measure, which will give you required output.
Hope this helps
-Sumit
Hi Sumit,
Thanks for the reply. The real problem which i'm trying to solve cannot be solved using a measure. It requires calculated columns and hence it is very necessary for me to get a derived DAX table.
I cannot share the real problem for the obvious reasons but measures cannot be used for the operations that i need to perform on the table.
@abhirajkakani wrote:
Hi Sumit,
Thanks for the reply. The real problem which i'm trying to solve cannot be solved using a measure. It requires calculated columns and hence it is very necessary for me to get a derived DAX table.
I cannot share the real problem for the obvious reasons but measures cannot be used for the operations that i need to perform on the table.
Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.
Measure actually may work, I think. You could declare calculated table variable and calculate against the variable, the variable would repsonse to the slicers.
Measure = VAR tempTable = CALCULATETABLE ( SUMMARIZE ( 'table', 'table'[Name], "TOTAL", SUM ( 'table'[Score] ) ) ) RETURN COUNTROWS ( tempTable )
Hello Kaushik,
Thanks for the reply. However, I need the date filter to have an effect on the derived table.
I just gave these tables for reference. My actual problem has a lot of calculations which require a Derived DAX table.
Let me explain my problem again.
I have a 'source table' and i'm summarizing that table using DAX queries to get the 'derived table'. I'm creating a lot of calculated columns on the 'derived table'.
Now, if i apply a filter on my 'source table' then what i want is that the 'derived table' should be re-calculated to just include the filtered 'source table'
Yup now i got your question.
Just give me some time to think.....
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 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |