- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Crete measure for multiple columns
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You may download my PBI file from here.
Hope this helps.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just adding to this
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
Regards
Miguel Félix
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your response and sorry I missed the steps. I will try and update the feedback to you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You are welcome. No you cannot.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Seanan @thanks for your quick reply. Could you please share your working file so I can test and update the feedback to you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
03-06-2024 02:12 PM | |||
07-11-2024 11:26 AM | |||
Anonymous
| 09-20-2017 05:51 AM | ||
07-22-2024 02:44 AM | |||
Anonymous
| 03-09-2023 08:36 AM |
User | Count |
---|---|
127 | |
83 | |
59 | |
57 | |
44 |
User | Count |
---|---|
183 | |
111 | |
82 | |
66 | |
51 |