Forum Discussion

PowerBI_TMO's avatar
PowerBI_TMO
Regular Visitor
7 years ago
Solved

comparing values in the same column by another column

The raw data:

I have a table with over 10 million rows of raw data, simliar like the sample data below:

column A has report date, colum C to F are product and region info. 

Column B is a concatenated column using column C to F. 

 

What I want to do with it:

I want to monitor the Unique_ID (Column B) by different report date (Column A) and see how the combination (i.e  Unique_ID) changes as day goes by. 

For example,in my report,

1. I want to be able to randomly pick two days (Oct 2nd and Oct 4th in the screenshot), 

2. and compare the Unique_IDs from Oct 2nd to the Unique_IDs from Oct 4th,

3. and FILTER OUT the same Unique_IDs in Oct 2nd and 4th (the GREEN and YELLOW highlighted ones will be taken out)

4. Keeping only the Unique_IDs in Oct 2nd that DO NOT show up in Oct 4th.

5. the final report would have a date filter like screenshot 2 and a table or matrix like screenshot 3 

 

 

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi PowerBI_TMO,

     

    Please check out the demo in the attachment. 

    You don't need to add the [measure] in the visual. You can add it in the Visual Level Filter.

    1. Create a Date table.

    2. Create a measure.

    Measure =
VAR maxSelectedDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR minSelectedDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    IF (
        MAX ( Table1[Report_date] ) = minSelectedDate
            && NOT MIN ( Table1[Unique_ID] )
                IN CALCULATETABLE (
                    VALUES ( Table1[Unique_ID] ),
                    FILTER ( ALL ( 'Table1' ), Table1[Report_date] = maxSelectedDate )
                ),
        1,
        BLANK ()
    )

    comparing-values-in-the-same-column-by-another-column

    Best Regards,
    Dale

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi PowerBI_TMO,

     

    Please check out the demo in the attachment. 

    You don't need to add the [measure] in the visual. You can add it in the Visual Level Filter.

    1. Create a Date table.

    2. Create a measure.

    Measure =
VAR maxSelectedDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
VAR minSelectedDate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )
RETURN
    IF (
        MAX ( Table1[Report_date] ) = minSelectedDate
            && NOT MIN ( Table1[Unique_ID] )
                IN CALCULATETABLE (
                    VALUES ( Table1[Unique_ID] ),
                    FILTER ( ALL ( 'Table1' ), Table1[Report_date] = maxSelectedDate )
                ),
        1,
        BLANK ()
    )

    comparing-values-in-the-same-column-by-another-column

    Best Regards,
    Dale