Forum Discussion

bourne2000's avatar
bourne2000
Icon for Helper V rankHelper V
4 years ago
Solved

How to implement intersect in the Power BI?

Hi

 

I am having the below table (My actual table size is 1 million records with multiple dates and times)

 

 

What do I want to do?

 

I want to find the same date and time for all the names with values greater than 0 and the sum of the value. I just did the same in excel as below

 

 

 

 

I need a measure to sum the 4+ 6+5 = 15. Can anyone advise how to calculate this measure? So that I can use the measure and date to visualize

 

Can anyone tell me how to do this?

 

Sample pbix file here  https://we.tl/t-2ancc6Zgcs

 

 

Need Help
Show and Tell
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Seeif this works for you. First the model:

     

    The measures:

    Sum Value = 
SUM('FTable'[Value])
    OK Values =
VAR _CR =
    COUNTROWS (
        FILTER (
            ALLEXCEPT ( 'FTable', 'Calendar Table'[dDate], 'time Table'[dTime] ),
            [Sum Value] <> 0
        )
    )
VAR _NN =
    CALCULATE ( DISTINCTCOUNT ( 'FTable'[Name] ), ALLSELECTED ( 'FTable' ) )
RETURN
    IF ( _CR = _NN, [Sum Value] )

    and the final measure to use in the matrix:

    Final Measure = 
IF (
    ISINSCOPE ( 'Time Table'[dTime] ),
    [OK Values],
    SUMX (
        SUMMARIZE ( FTable, 'Time Table'[dTime], 'Calendar Table'[dDate] ),
        [OK Values]
    )
)

    To get:

    I've attached the sample PBIX file

8 Replies

    • bourne2000's avatar
      bourne2000
      Icon for Helper V rankHelper V
      4 years ago

      ribisht17 Thanks. I need it as a measure. So I can visualize measure vs date. Can you help? measure should be all the sum of values with greater than 0 and same date &time

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    You can achive by doing these two things:

    1. Format the date and time from text 

    2. Create a measure like this and use instead of Vale.

    Measure = if(sum('Table'[Value])>0,sum('Table'[Value]))
  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Seeif this works for you. First the model:

     

    The measures:

    Sum Value = 
SUM('FTable'[Value])
    OK Values =
VAR _CR =
    COUNTROWS (
        FILTER (
            ALLEXCEPT ( 'FTable', 'Calendar Table'[dDate], 'time Table'[dTime] ),
            [Sum Value] <> 0
        )
    )
VAR _NN =
    CALCULATE ( DISTINCTCOUNT ( 'FTable'[Name] ), ALLSELECTED ( 'FTable' ) )
RETURN
    IF ( _CR = _NN, [Sum Value] )

    and the final measure to use in the matrix:

    Final Measure = 
IF (
    ISINSCOPE ( 'Time Table'[dTime] ),
    [OK Values],
    SUMX (
        SUMMARIZE ( FTable, 'Time Table'[dTime], 'Calendar Table'[dDate] ),
        [OK Values]
    )
)

    To get:

    I've attached the sample PBIX file