Forum Discussion

Eng-mohd's avatar
Eng-mohd
Helper I
2 years ago
Solved

undefined power bi

I have sheets  Sheet1  have id and its duration time  Sheet2 have id and its duration time    I wana calculate how many person take mor than 119 minutes    And i wana sure there is no repeated ...
  • some_bih's avatar
    2 years ago

    Hi Eng-mohd if your sheet 1 and sheet 2 have the same structure,  and data for durations is in minutes (same in two sheets) possible solution (adjsust sheets / column names to your need)
    1. Create new table 

    CombinedTable = UNION(Sheet1, Sheet2)

    2. Create measure

    Persons_More_Than119Minutes =
    CALCULATE(
    COUNTROWS(DISTINCT(CombinedTable[ID])),
    CombinedTable[Duration] > 119
    )

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Eng-mohd ,

    You can create a measure as below to get it, please find the details in the attachment.

    Measure = 
    VAR _sheet1 =
        CALCULATETABLE (
            VALUES ( 'Sheet1'[id] ),
            FILTER ( 'Sheet1', 'Sheet1'[duration time] > 119 )
        )
    VAR _sheet2 =
        CALCULATETABLE (
            VALUES ( 'Sheet2'[id] ),
            FILTER ( 'Sheet2', 'Sheet2'[duration time] > 119 )
        )
    VAR _tab =
        DISTINCT ( UNION ( _sheet1, _sheet2 ) )
    RETURN
        COUNTROWS ( _tab )

    Best Regards