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
Hi All,
I have requirement to create a P_Flag column and use in slicer.
When any one C_code associated with P_Codes have C_flag as Y then whole P_Flag should be assigned Y. Please find the O/P required below.
Table1
P_Code | C_Code | Amt |
8G1 | 318 | 60 |
8G1 | 458 | 30 |
8G1 | 314 | 80 |
G54 | 750 | 40 |
G54 | 547 | 20 |
G54 | 458 | 50 |
TY7 | 159 | 10 |
Table2
C_Code | C_Flag |
318 | Y |
458 | N |
314 | N |
750 | N |
547 | N |
159 | Y |
Output
P_Code | C_Code | Amt | O/P P_Flag |
8G1 | 318 | 60 | Y |
8G1 | 458 | 30 | Y |
8G1 | 314 | 80 | Y |
G54 | 750 | 40 | N |
G54 | 547 | 20 | N |
G54 | 458 | 50 | N |
TY7 | 159 | 10 | Y |
Thank you in advance
Solved! Go to Solution.
Hi,
For this you need to create a disconnected table with the filter to be used in this case Y/N.
The create the following measure:
Flag_MEasure_filter = IF([P FLag Measure] in DISTINCT('Flag Filter'[Flag filter]), 1)
Use this measure to filter the table and the column of the disconnected table to use has slicer result below and in attach file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi All,
Please let me know if the requirement is posiible at all I have been struggling with this for some time.
Thank you
Hi @praveenpasila ,
Try the following code:
Flag =
VAR P_CodeSelection = Table1[P_Code]
VAR temp_table =
SUMMARIZE (
ADDCOLUMNS (
FILTER ( DISTINCT(Table1[P_Code]), [P_Code] = P_CodeSelection ),
"FlagValue", RELATED ( Table2[C_Flag] )
),
[FlagValue]
)
RETURN
IF (
CONTAINSSTRING ( CONCATENATEX ( temp_table, [FlagValue] ), "Y" ) = TRUE (),
"Y",
"N"
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Thank you so much for the reply.
I see the new column is bringing the flag into the Table1 but I would like to add as column is bring new column and assign Y
even if one C_code has Y then assing Y for the whole P_code as below
I made a code as below which is working fine but not considering when date selected
Step1: created measure in Table2
Step2: created column in Table1
P_Code | C_Code | Amt | Date |
8G1 | 318 | 60 | 11/15/2022 |
8G1 | 458 | 30 | 11/15/2022 |
8G1 | 314 | 80 | 11/15/2022 |
G54 | 750 | 40 | 11/15/2022 |
G54 | 547 | 20 | 11/15/2022 |
G54 | 458 | 50 | 11/15/2022 |
TY7 | 159 | 10 | 11/15/2022 |
8G1 | 458 | 30 | 11/14/2022 |
8G1 | 314 | 80 | 11/14/2022 |
G54 | 750 | 40 | 11/14/2022 |
G54 | 547 | 20 | 11/14/2022 |
G54 | 458 | 50 | 11/14/2022 |
TY7 | 159 | 10 | 11/14/2022 |
C_Code | Flag |
318 | Y |
458 | N |
314 | N |
750 | N |
547 | N |
159 | Y |
Please suggest and help with column if possible to achieve
Hi @praveenpasila ,
I got lost about what is your need.
You want to have a dynamic calculation of the Y/N based on the date selected?
If that is the case you need a measure and not a column because columns are static calculations, I also need to see how you have the connection with the date values.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @MFelix for the update.
The data is same with simple date table joined on datekey similar to below
Table1
P_Code | C_Code | Amt | Datekey |
8G1 | 318 | 60 | 20221115 |
8G1 | 458 | 30 | 20221115 |
8G1 | 314 | 80 | 20221115 |
G54 | 750 | 40 | 20221115 |
G54 | 547 | 20 | 20221115 |
G54 | 458 | 50 | 20221115 |
TY7 | 159 | 10 | 20221115 |
8G1 | 458 | 30 | 20221114 |
8G1 | 314 | 80 | 20221114 |
G54 | 750 | 40 | 20221114 |
G54 | 547 | 20 | 20221114 |
G54 | 458 | 50 | 20221114 |
TY7 | 159 | 10 | 20221114 |
Table2
C_Code | Flag |
318 | Y |
458 | N |
314 | N |
750 | N |
547 | N |
159 | Y |
Date
Datekey | Calendar date |
20221115 | 11/15/2022 |
20221114 | 11/14/2022 |
Relatioship
Similar to column I created a measure as below which is working fine with dates
But again the problem is the C_Flag column selection creates problem
Please see below picture when I select Flag as N the P_Flag_Measure is not worknig fine
C_codes 314 and 458 should show Y but its changing.
In the above image its showing Y and after Flag selection its showing N as below image
Please share you inputs. Thank you
Hi,
For this you need to create a disconnected table with the filter to be used in this case Y/N.
The create the following measure:
Flag_MEasure_filter = IF([P FLag Measure] in DISTINCT('Flag Filter'[Flag filter]), 1)
Use this measure to filter the table and the column of the disconnected table to use has slicer result below and in attach file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Sorry if I confused the main criteria is to use both flags child Flag and
Parent Flag ( new column or measure created)
user needs both Flags for slicer selection.
When we select both Flags to Y its good but when we select N in both Flags we should see only 3 C_codes but we are getting 5 C_codes
As the criteria is we assign Y to complete parent even one child code has Y in child flag which is in Table2 since 8G1 has one C_code with Child Flag as Y we assign whole parent Flag Y
When we select both Flags N we expect only G54 to be displayed as it is not having even one c_code with Y in Child Flag
please see the image below we don't expect the highlighted once when both Flags are set to N.
please suggest if this is fesiable.
Thank you so much for all your help.
Hi @praveenpasila ,
Clarify me please about the objective of getting the flag slicer, I understood that you want to filter the flag measure by the Y/N meaning that if you select Y the result based on your first image would be:
His my perception correct? If this is correct then you only need to use the new table has a slicer on the page since the new metric compares the selection of the slicer to the result of the metric.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYou are absolutely right @MFelix everything works fine when individually selected until users are selecting both flags in combination
Ex:
Select N from Flag column in Table2 and Y from new Table then we expect to see P_code 8G1 and C_Codes 314 and 458
Select N from Flag column in Table2 and N from new Table then we expect to see P_code G54 and C_Codes 458,547 and 750
I see the measure is changing when we select Flag N from Table2 seems like the measure is considering that particular subset of data and assigning N for
C_Codes 314 and 458
may be adding allexcept on the 'Table (2)'[Flag] in the "P FLag Measure" might help
sorry however not sure how to add it 😊
Hi @praveenpasila ,
Why are you using the flag from table 2? In this case since you wnat to go over the filter context of the table2 you must not use that column in your slicers/filters or within your visualization because that will break your measures.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
The reason is users might either select slicer from Table 2 and download the data all child codes affiliated with it or directly select P_flag new colum we created and download data where atleast one child is affiliated.
sometimes they might select combination of slicers.
So the conclusion is we would not be able to do that since we will breaking the measure right?
Sorry,
Once again just trying to understand the use case.
Is this dataset being build for users to create their own report?
Don't understand how the use of the two slicers works for them.
Isn't the purpose of this calculation to be done so that you get the Y or N base on having at least one Y on the c_flag?
Or do you want to see the values also based on the yes or n of the c_flag no matter what is the group for the new measure we did?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
yes we use this dataset for both to create static reports and also users to create customized reports.
-- Isn't the purpose of this calculation to be done so that you get the Y or N base on having at least one Y on the c_flag?
Yes the purpose is same as you mentioned that we get the Y or N base on having at least one
Y on the c_flag which is achieved by measure or the new table method you suggested.
but as I mentioned the users want both flags as slicer
If the scenario is not posssible also its ok just making sure if we can achieve it or not so we can convey the users to select the slicers individually.
Thank you again for all your help @MFelix
Hi @praveenpasila ,
When you use the values from the column on a slicer or filter you are reducing the data on your model, so if you pick up the NO from table two the 159 will not appear in the values since it's yes.
I know you have sent out an example but I still not getting, what is the result you want to get. Has refered when you filter the information from the tables you filter the data, we can skip the filter context however believe that the issue can be at an lower level and the use of the measures and calculated columns is changing that context.
If you don't mind can you give (again I know) an example of the results you want to have and how you want the setup of the report?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsGoed!!
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 | |
96 | |
91 | |
82 | |
69 |
User | Count |
---|---|
159 | |
125 | |
116 | |
111 | |
95 |