Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a whole series of measures that are all coming from a single table. I am trying to figure out how I can display them in a table where I input the row name and assign a metric as a value. I know I can put metrics into a table but that just gives me the values across columns.
So the numbers next to Conv, UPT, AOV etc. are all measures being calculated off of the table above. The Row labels are just text boxes. They are currently all being shown as Cards which is super cumersome to work with because I need to create 3-4 more sets and make sure each set is aligned/filtered individually.
I can't figure out any way to get them into a matrix or table via DAX.
The format of the table is:
Row_Lbl | Col_Lbl | Date | Value
1) Dmnd-Ops | TY | 2/4/2018 | 248.15
1) Dmnd-Ops | LY | 2/4/2017 | 150.50
7) Sessions | TY | 8/20/2018 | 14585
😎 Orders | LY | 10/10/2017 | 84
Thanks
Solved! Go to Solution.
I was able to figure this out:
First I create a table in Edit Queries for each of my KPI's, one each for "TY" and "LY" (This Year/Last Year). The table just has a Column1 with the number 1 in it.
I place a Measure filtered for TY into the new "TY" table. I also add new Columns, one for the row lable ("Conv") and column lable ("TY"). Finall I add a column that is = to the measure I had just added because the DAX UNION function I will use later doesn't recognize Measures.
I did the same for the "LY" data.
Finally I use a UNION function to append the 2 tables into a 3rd new table so the column labels, row labels and the measure data that is sitting in a column line up. Function is "create new Table" and KPI_Combo = UNION(KPI_Conv_TY,KPI_Conv_LY)
After doing this I can generate the table at the bottom:
Now I can just do the same with other KPI's, i.e. create a table for each and UNION them into the KPI_Combo table.
Hey,
I'm not sure if I understand your requirement correctly but maybe you can the matrix visual a try and then use the option "Show on rows" from values band:
Hopefully this is what you are looking for.
Regards
Tom
I was able to figure this out:
First I create a table in Edit Queries for each of my KPI's, one each for "TY" and "LY" (This Year/Last Year). The table just has a Column1 with the number 1 in it.
I place a Measure filtered for TY into the new "TY" table. I also add new Columns, one for the row lable ("Conv") and column lable ("TY"). Finall I add a column that is = to the measure I had just added because the DAX UNION function I will use later doesn't recognize Measures.
I did the same for the "LY" data.
Finally I use a UNION function to append the 2 tables into a 3rd new table so the column labels, row labels and the measure data that is sitting in a column line up. Function is "create new Table" and KPI_Combo = UNION(KPI_Conv_TY,KPI_Conv_LY)
After doing this I can generate the table at the bottom:
Now I can just do the same with other KPI's, i.e. create a table for each and UNION them into the KPI_Combo table.
Thanks @sxt173 , this is the closest solution to the problem that I have seen all over the internet. I used this and it worked fine except for one small thing. My KPIs use time intelligence function, (the measures in original tables use Calendar[Date] in YTD and LM measures) and in absence of date field in the new tables, the measures do not give an exact value. Any idea/thoughts for solving this? Would be really helpful.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |