Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have two tables are table 1 and table 2.
In table 1 the following columns are contains description, qty need 10 days, qty need 20 days, qty need 30 days and qty need 40 days.
In table 2 has days only 4 rows.
There's no technical connection in between two tables so I don't known how can I link together in order to achieve the result in visualisation.
Desired result and Example.
I apply the slicer for table 2 for days so
If I select 10 days then it will show only sum of qty of 10 days and the same thing for rest of the days.
I would like achieve the result in visualisation.
I am looking for measure or new calculate column in order to link in between two tables.
Snapshot of tables and desired result.
Solved! Go to Solution.
Hi @Saxon10
I have a solution but it is not the most dynamic. If your column headers will never change then this may be a fine solution but I'm sure someone may have a more dynamic option. However, please see the below measure and let me know if this is a viable option.
CheckQty = SWITCH(TRUE(),
CONTAINSSTRING("Qty Need 10 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 10 Days]),
CONTAINSSTRING("Qty Need 20 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 20 Days]),
CONTAINSSTRING("Qty Need 30 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 30 Days]),
CONTAINSSTRING("Qty Need 40 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 40 Days])) + 0
Result:
Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.
Hi @Saxon10
You could try this:
Total = 'Items'[10 Days] + 'Items'[20 Days] + 'Items'[30 Days] + 'Items'[40 Days]
Then click on the parameter column and adjust the code to:
Days = {
("10Days", NAMEOF('Items'[10 Days]), 0),
("20 Days", NAMEOF('Items'[20 Days]), 1),
("30 Days", NAMEOF('Items'[30 Days]), 2),
("40 Days", NAMEOF('Items'[40 Days]), 3),
("Total", NAMEOF('Items'[Total]), 4)
}
Result:
Hi @Saxon10 ,
For the card create the following measure:
Measure =
VAR __SelectedValue =
SELECTCOLUMNS (
SUMMARIZE ( days , Days[Days] , Days[Days Fields]),
Days[Days]
)
var SelectedValuesDays = CONCATENATEX(__SelectedValue, Days[Days], "|")
Return
IF(CONTAINSSTRING(SelectedValuesDays, "10"), [10 days])
+ IF( CONTAINSSTRING(SelectedValuesDays, "20"), [20 days])
+ IF( CONTAINSSTRING(SelectedValuesDays, "30"), [30 days])
+ IF( CONTAINSSTRING(SelectedValuesDays, "40"), [40 days])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur, Thanks for your reply, those qty columns came from dax not part of the data source therefore unable to unpivot the data.
can I get the same output without unpivot the data source.
Please advice
Hi @Saxon10 ,
The option given by @Seanan however and with the new parameter fileds you can have a dynamica table that shows all the values directly:
Create a sum measure for each column:
Now create the parameters
Now you can have a dynamic table
If you place it on a card you will have the first one selected also the order you select the values in the slicer is the order of the table:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix, Thanks for your response.
Some reason it's not working properly.
Example :
If I select 30 days then value not changing its showing 20 days value instead off 30 days. Please refer the snap shot of the error.
My expectation is if I select 10 days then it will show only 10 days sum of qty the same thing for 20, 30 and 40 days.
On the card you need to use the column from the parameter field you used, since it's a card will present the first one selected on the slicer.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
I don't have parameters new field option like you have it.
(Highlights in red colour) I don't have any drop down option at my end in order to choose/add the field option.
Is there any setting need to be enabled? Can you please share the step by step article so I can get the same option.
Even I can't see the option your work file.
How can I add the parameter in card. I am really struggling and its confusing for me.
@Seanan option working only first selection and when I try to choose different days in slicer then sum of qty is blanks.
I got the parameter option at my end(Please refer the snapshot)
Is there any alternative way I can achieve the desired results.
Could you please advise.
Much appreciated your effort and time.
Thank you
Hi @Saxon10
You can enable field parameters by clicking File -> Options and Settings -> Options -> Preview Features -> Tick field parameter (Thanks @MFelix for showing me this)
Just adding to this
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Saxon10 ,
For the card create the following measure:
Measure =
VAR __SelectedValue =
SELECTCOLUMNS (
SUMMARIZE ( days , Days[Days] , Days[Days Fields]),
Days[Days]
)
var SelectedValuesDays = CONCATENATEX(__SelectedValue, Days[Days], "|")
Return
IF(CONTAINSSTRING(SelectedValuesDays, "10"), [10 days])
+ IF( CONTAINSSTRING(SelectedValuesDays, "20"), [20 days])
+ IF( CONTAINSSTRING(SelectedValuesDays, "30"), [30 days])
+ IF( CONTAINSSTRING(SelectedValuesDays, "40"), [40 days])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Saxon10
You could try this:
Total = 'Items'[10 Days] + 'Items'[20 Days] + 'Items'[30 Days] + 'Items'[40 Days]
Then click on the parameter column and adjust the code to:
Days = {
("10Days", NAMEOF('Items'[10 Days]), 0),
("20 Days", NAMEOF('Items'[20 Days]), 1),
("30 Days", NAMEOF('Items'[30 Days]), 2),
("40 Days", NAMEOF('Items'[40 Days]), 3),
("Total", NAMEOF('Items'[Total]), 4)
}
Result:
Thanks for your response and sorry I missed the steps. I will try and update the feedback to you.
You are welcome. No you cannot.
Hi @Saxon10
I have a solution but it is not the most dynamic. If your column headers will never change then this may be a fine solution but I'm sure someone may have a more dynamic option. However, please see the below measure and let me know if this is a viable option.
CheckQty = SWITCH(TRUE(),
CONTAINSSTRING("Qty Need 10 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 10 Days]),
CONTAINSSTRING("Qty Need 20 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 20 Days]),
CONTAINSSTRING("Qty Need 30 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 30 Days]),
CONTAINSSTRING("Qty Need 40 Days",MAX('Days'[Days])),SUM('Items'[Qty Need 40 Days])) + 0
Result:
Kind regards,
Seanan
If this post helped, please consider accepting it as the solution.
@Seanan, I try to apply your measure logic in actual data and some reason measure working only qty needs 10 days and rest of them is showing 0 when I try to choose qty need 20 days, 30 days and 40 days I don't know why?
I have a lot blanks columns in each columns maybe that's the reason it's not calculated properly?
Can I get new calculate column instead of measure? is that possible?
in your sample data file working without any issues.
can you please advise.
Hi @Saxon10
Thanks for letting me know.
I'll take a look at changing the code to fit in a calculated column. However, I am a little busy today so I'll get back to you later this evening.
@Seanan @thanks for your quick reply. Could you please share your working file so I can test and update the feedback to you.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
92 | |
86 | |
76 | |
66 |
User | Count |
---|---|
146 | |
111 | |
109 | |
103 | |
96 |