Help with Flag Column

[praveenpasila]

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

[praveenpasila]

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]

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

[praveenpasila]

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

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_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

[MFelix]

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.

[praveenpasila]

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

[MFelix · 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:

[praveenpasila]

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.

Thank you so much for all your help.

[MFelix]

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.

[praveenpasila]

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

may be adding allexcept on the 'Table (2)'[Flag] in the "P FLag Measure" might help

sorry however not sure how to add it 😊

[MFelix]

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.

[praveenpasila]

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?

[MFelix]

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? 

[praveenpasila]

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 

[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?

[praveenpasila]

Thank you  @MFelix, for the help

[RaphaëlGargie]

Goed!!