Forum Discussion

aanyoti's avatar
aanyoti
Helper I
5 years ago
Solved

Compare two columns two tables and count difference

Hi, I need help.

 

I have two tables(Zonal Report and Zones) below with a many to one relationship. The Zonal Report has entries entered per week.

 

 

 

I would like to create a measure that will count how many Zones and Cells were not entered for a particular week. I also wanted another measure that would list the names of the Zones and Cells not submitted for a particular week. I tried using the measure below with no success, would appreciate any guidance.

 

 

Occur = 
    VAR SubmittedCells = 
           IF(
                HASONEVALUE('Zonal Report'[Cells]),
                VALUES('Zonal Report'[Cells]),
                BLANK()
                )
    RETURN
        CALCULATE(
            COUNTROWS(Zones),
            FILTER(
                Zones,
                Zones[Cells] <> SubmittedCells
            )
        )

 

  • aanyoti , asuuming filter is coming from date . Use this measure with columns of zone table or in card

     

    countx(values(Zone[Cells]),if( isblank(calculate(countows('Zonal Report'))), [Cells],blank()))

2 Replies

  • aanyoti , asuuming filter is coming from date . Use this measure with columns of zone table or in card

     

    countx(values(Zone[Cells]),if( isblank(calculate(countows('Zonal Report'))), [Cells],blank()))

    • aanyoti's avatar
      aanyoti
      Helper I

      Thanks so much amitchandak , this worked!

       

      I have learnt something valuable using the COUNTX function.

       

      Thanks again!😎