Forum Discussion

Dunner2020's avatar
Dunner2020
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Table shows more information than expected

Hi there,

I have created two measures that count the no of event occurred w.r.t some criteria. If the count of occurrence is more than some threshold value then it displays information. My measures are

Trigger 1 =
Var _cause = MAX( ‘Fact Table’[Cause] )
Var _loc = MAX( ‘Fact Table’[CircuitID] )
Var _zone = MAX(‘Fact Table’[ZoneSubstation])
Var _mindate = EDATE(EOMONTH(TODAY(),0),-[N Value])
var _count = CALCULATE(DISTINCTCOUNT(‘Fact Table’[OutageID]),FILTER(‘Fact Table’,‘Fact Table’[CircuitID] ==_loc && ‘Fact Table’[Cause] == _cause &&‘Fact Table’[ZoneSubstation] == _zone && ‘Fact Table’[Actual Interruption Start Time (NZST)]>=_mindate))
Return
IF(_count>[Trigger Value Value],_count)

Trigger 2 =
Var _equip = MAX( ‘Fact Table’[Equipment] )
Var _mindate = EDATE(EOMONTH(TODAY(),0),-[N Value])
var _count = CALCULATE(DISTINCTCOUNT(‘Fact Table’[OutageID]),FILTER(‘Fact Table’,‘Fact Table’[Equipment] ==_equip && ‘Fact Table’[Actual Interruption Start Time (NZST)]>=_mindate))
Return
IF(_count>[Trigger Value Value],_count)

I displayed these two measures in separate tables visual as shown in the picture:

 

 

Now I want to see the details of the values shown in those tables. So, I created a third table that shows the details about the event.

The problem is that when I click any of the values showing in the first two tables, the third table shows more information than expected. For example, as shown in the following picture:

When I click on the first row (where the Trigger 1 value is 5) of the first table, the expected output in the third table (detailed table) is that it only shows the value of those five events and the distinct count of outage id should be five. However, in the above picture as you can see that distinct count of outage id is 7. I am not sure why is it displaying information of 7 events instead of 5. Any help would be really appreociated.

 

Sample file can be download from here.

  • Dunner2020 

    Your [Trigger 1] measure has as part of it's calcualtion a date component.  Only counting items that are on or after the month based on the 'Last N Months' selection.  Your detail table does not have any sort of date filter so when you select and item in the summary table, the only filters that are moving to the detail table are

    • 'Station'[SubstationCode]
    • 'Code'[CODE]
    • 'Cause'[Code]

    You can add a measure like this to pick up the selected months and apply it as a filter to the detail table.

    Detail Filter = 
    VAR _Fact =
        COUNTROWS ( 'Fact Table' )
    VAR _MinDate =
        EOMONTH ( TODAY (), - [N Value] )
    VAR _LineDate =
        SELECTEDVALUE ( 'Fact Table'[Date] )
    RETURN
        IF ( NOT ISBLANK ( _Fact ), IF ( _LineDate >= _MinDate, 1, BLANK () ) )

    You have some extra code in some of your measres.  These two lines will give you the same results.

    _mindate = EDATE(EOMONTH(TODAY(),0),-[N Value])
    _mindate = EOMONTH(TODAY(),-[N Value])

    You should also turn off Auto Date Time and add a calendar table to your model and hook it to the date column in your Fact table.  Because you have dates going back to 1900 in 5 columns 90% of your model is taken up by auto date time columns.

    I have added the measure and made the date tweaks to your file and attached my updated copy.

     

     

     

     

1 Reply

  • Dunner2020 

    Your [Trigger 1] measure has as part of it's calcualtion a date component.  Only counting items that are on or after the month based on the 'Last N Months' selection.  Your detail table does not have any sort of date filter so when you select and item in the summary table, the only filters that are moving to the detail table are

    • 'Station'[SubstationCode]
    • 'Code'[CODE]
    • 'Cause'[Code]

    You can add a measure like this to pick up the selected months and apply it as a filter to the detail table.

    Detail Filter = 
    VAR _Fact =
        COUNTROWS ( 'Fact Table' )
    VAR _MinDate =
        EOMONTH ( TODAY (), - [N Value] )
    VAR _LineDate =
        SELECTEDVALUE ( 'Fact Table'[Date] )
    RETURN
        IF ( NOT ISBLANK ( _Fact ), IF ( _LineDate >= _MinDate, 1, BLANK () ) )

    You have some extra code in some of your measres.  These two lines will give you the same results.

    _mindate = EDATE(EOMONTH(TODAY(),0),-[N Value])
    _mindate = EOMONTH(TODAY(),-[N Value])

    You should also turn off Auto Date Time and add a calendar table to your model and hook it to the date column in your Fact table.  Because you have dates going back to 1900 in 5 columns 90% of your model is taken up by auto date time columns.

    I have added the measure and made the date tweaks to your file and attached my updated copy.