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'm terribly sorry if this has been asked multiple times however I can't seem to find a quick solution to this query.
I have a dataset that has multiple columns, two of these columns are number columns with numbers between 1-12 for months (whole number). My data might look something like this:
Col1 Col2 Col3 Col4
fywh 12 3 £100
hujq 10 5 £501
I need to create a line graph with col2 & col3 as the bottom axis, col1 as the y axis and col4 as the value. Easy enough however I need to be able to toggle between Col2 & Col3 to reflect the cost at the different months.
How I've done this is to add a custom column to merge Col2 & Col3 with an identifying in between. Such as: 12, Col2; 3, Col3
After much Power Query magic I basically managed to duplicate all lines of my table so it now looks like this:
(I followed steps as per this page to action this: https://community.powerbi.com/t5/Desktop/How-do-I-duplicate-rows-based-on-cell-contents-cell-contain...
Col1 Col2 Col4 Col5
fywh 12 £100 Col2
fywh 3 £100 Col3
hujq 10 £501 Col2
hujq 5 £501 Col3
This is great because it allows me to create the line graph that I want and set a filter for Col5 to filter the dataset...in theory and small practice yes...
The problem I have is that in reality my dataset is over 2 million lines (and counting) so to action this each refresh the report just can't copy.
There must be a simpler way of actioning this. I was thinking of a filter table with the identifyers in then a DAX measure to filter across Col2 and Col3 that way. Would this be the best thinking? I have tried a relationship but I can't see how this would work as there isn't any way of connecting to the main data (I would have to create the identifyer column to do this)
I'm sorry if this doesn't make sense!
E
Solved! Go to Solution.
HI @Anonymous,
#1, Unpivot columns show be a better choice to handle multiple value field interactions, but it will expand your table records so it should not suitable to work with tables that include larger amount of records.
For this scenario, you can consider extracting the category field and value fields to a new table that only includes the key fields, then do unpivot on it. (it should better than direct expand raw tables records)
#2. According to your description, it seems like you are trying to create a dynamic field that based on filter selection. For this requirement, you need to create a parameter table that stored the value field names, then you can use it on the switch function to show different table field values based on current selections.
Measure =
// parameter table stored A,B,C,D types of field name
VAR selected =
SELECTEDVALUE ( ParaTable[Field Name] )
RETURN
SWITCH (
selected,
"A", SUM ( Table[Field A] ),
"B", SUM ( Table[Field B] ),
"C", SUM ( Table[Field C] ),
"D", SUM ( Table[Field D] ),
//default and exception case
SUM ( Table[Field A] )
)
BTW, current power bi does not support creating a dynamic calculated column/table based on filter selections. Please use the measure formula instead.
Regards,
Xiaoxin Sheng
Use Calculation Groups.
Hi,
Do you know where I can read more into this please?
Thanks
E
HI @Anonymous,
You can take a look at the following methods if they suitable for your scenario:
1. Unpivot columns.
Enable 'unpivot columns' on your value fields to convert them to attribute and value, then you can simply filter these records.
Unpivot columns (Power Query) - Excel (microsoft.com)
2. Calculate group and measure filter.
Introducing Calculation Groups
Applying a measure filter in Power BI - SQLBI
Regards,
Xiaoxin Sheng
@v-shex-msft Hi, thank you. I've taken a read to both of your solutions.
The 'Unpivot Columns' idea did work to begin with which was fantastic but when I tried to load in all the data that was needed into the tool its kept timing out during refresh.
I decided to re-think my options to allow for the large dataset and I've come up with the following solution in Excel that works:
Col1 Col2 Col3 Col4 NewCol5
fywh 12 3 £100 (=IF(Table2 Filter="Col2", B2,IF((H2 Filter="Col3",C2, ""))
hujq 10 5 £501 (=IF(Table2 Filter="Col2", B3,IF((H2 Filter="Col3",C3, ""))
I then plan on creating a line graph with Col5 as the Axis, Col4 as the value and a year column in the legend.
I know that Power BI gets funny with IF statements so I'm unsure how to get around this? I've created a new Filter Table with simply "Col2","Col3" listed (with an index) and I've created an index column in my main table. I've created a relationship between the two tables through the index column.
Do you know where I'd go from here please?
Thanks!
E
HI @Anonymous,
#1, Unpivot columns show be a better choice to handle multiple value field interactions, but it will expand your table records so it should not suitable to work with tables that include larger amount of records.
For this scenario, you can consider extracting the category field and value fields to a new table that only includes the key fields, then do unpivot on it. (it should better than direct expand raw tables records)
#2. According to your description, it seems like you are trying to create a dynamic field that based on filter selection. For this requirement, you need to create a parameter table that stored the value field names, then you can use it on the switch function to show different table field values based on current selections.
Measure =
// parameter table stored A,B,C,D types of field name
VAR selected =
SELECTEDVALUE ( ParaTable[Field Name] )
RETURN
SWITCH (
selected,
"A", SUM ( Table[Field A] ),
"B", SUM ( Table[Field B] ),
"C", SUM ( Table[Field C] ),
"D", SUM ( Table[Field D] ),
//default and exception case
SUM ( Table[Field A] )
)
BTW, current power bi does not support creating a dynamic calculated column/table based on filter selections. Please use the measure formula instead.
Regards,
Xiaoxin Sheng
Hello,
Thank you for getting back to me on this, I'm sorry I didn't sooner however I took a period of leave so didn't think about this! I will certainly give this a try, thank you. The only other option I found that worked was bookmarks.
Thanks,
E
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 |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |