cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
praveenpasila
Advocate IV
Advocate IV

Help with Flag Column

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_CodeC_CodeAmt
8G131860
8G145830
8G131480
G5475040
G5454720
G5445850
TY715910

 

Table2

C_CodeC_Flag
318Y
458N
314N
750N
547N
159Y

 

Output

 

P_CodeC_CodeAmtO/P P_Flag
8G131860Y
8G145830Y
8G131480Y
G5475040N
G5454720N
G5445850N
TY715910Y

 

praveenpasila_0-1677505087623.png

 

Thank you in advance

 

1 ACCEPTED 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:

 

MFelix_0-1677785829544.png

 

MFelix_1-1677785844459.png

 


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



View solution in original post

16 REPLIES 16
praveenpasila
Advocate IV
Advocate IV

Hi All,

 

Please let me know if the requirement is posiible at all I have been struggling with this for some time.

 

Thank you

MFelix
Super User
Super User

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"
    )

MFelix_0-1677664703702.png

 


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



Hi @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

 

praveenpasila_0-1677666319697.png

I made a code as below which is working fine but not considering when date selected 

 

Step1: created measure in Table2

Flag Measure = IF(MAX(Table2[Flag]) = "Y",1,0)

Step2: created column in Table1

Flag_new column =
var __pcode = Table1[P_Code]
var __max   = maxx(FILTER(ALL(Table1),Table1[P_Code] = __pcode),[Flag Measure])
return
IF(__max = 1,"Y","N")
 
Sorry missed date column date is coming from different table though but the please check below data sample below

 

P_CodeC_CodeAmtDate
8G13186011/15/2022
8G14583011/15/2022
8G13148011/15/2022
G547504011/15/2022
G545472011/15/2022
G544585011/15/2022
TY71591011/15/2022
8G14583011/14/2022
8G13148011/14/2022
G547504011/14/2022
G545472011/14/2022
G544585011/14/2022
TY71591011/14/2022

 

C_CodeFlag
318Y
458N
314N
750N
547N
159Y

 

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


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



Thanks @MFelix for the update.

 

The data is same with simple date table joined on datekey similar to below

Table1

P_CodeC_CodeAmtDatekey
8G13186020221115
8G14583020221115
8G13148020221115
G547504020221115
G545472020221115
G544585020221115
TY71591020221115
8G14583020221114
8G13148020221114
G547504020221114
G545472020221114
G544585020221114
TY71591020221114

 

Table2

C_CodeFlag
318Y
458N
314N
750N
547N
159Y

 

Date

DatekeyCalendar date
2022111511/15/2022
2022111411/14/2022

 

Relatioship

praveenpasila_0-1677671645344.png

 

Similar to column I created a measure as below which is working fine with dates 

 

praveenpasila_1-1677671930373.png

 

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

 

praveenpasila_2-1677672181451.png

 

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:

 

MFelix_0-1677785829544.png

 

MFelix_1-1677785844459.png

 


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



Hi @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.

praveenpasila_0-1677837097630.png

 

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:

 

MFelix_0-1677848838199.png

 

 

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


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



You 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

 

praveenpasila_1-1677854021571.png

 


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


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



Hi @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


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



Hi @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


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



Thank you  @MFelix, for the help

Goed!! 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors