Calculate multiple table filter

[Ginjko]

Hi there.

I am trying to display a matrix as an heat map, everything works fine but I would like to filter some data imput errors through a measure and I cannot figure out an answer

I have manage this visual

 This is a matrix With

Columns coming from the date dimension table , [day of week]

Rows coming from the clock dimension table, [working hour]

Values coming from a measure, [Average daily count] relating to the fact table 

My problem is that there are datas in the fact table wich have been entered incorrectly and for sunday on the 8th hour we are supposed to be closed and the value should be blank.

The best solution would be to edit the data source but I cannot do that.

I am trying to edit my measure to ignore data for sundays on the 8th hour but i have not succeded 

This is my original measure, the one displayed on the matrix

Average daily count =

VAR NumberOfTransactions = COUNT('Ticketing fact table exports'[ Ticket Number])

VAR NumberOfDays =

CALCULATE(

    COUNTROWS('Date'),'Date'[DatesWithSales] = True

)

Var Result =   DIVIDE(NumberOfTransactions,NumberOfDays)

Return

Result

And this is my best shot so far, unfortunatly it doesn't work it only hides data for all sundays and all of the 8th hours, like all my other trys

Correct Average daily count try =

VAR NumberOfTransactions = CALCULATE(COUNT('Ticketing fact table exports'[ Ticket Number]), FILTER('Ticketing fact table exports', RELATED(Clock[Working Hour])<>8 && RELATED('Date'[Day of Week Number]) <> 7))

VAR NumberOfDays =

CALCULATE(

    COUNTROWS('Date'),'Date'[DatesWithSales] = True

)

Var Result =   DIVIDE(NumberOfTransactions,NumberOfDays)

Return

Result

Any help would be greatly appreciated

[v-jingzhang]

Hi @Ginjko 

Your measure is close to the expected result except for a small logic error in it. You should replace the operator "&&" with "||" in your measure. As with "&&", it filter out the rows with either working hour is 8 or day of week number is 7, however these rows should be kept in the calculation. With "||", it will only filter out the rows with working hour is 8 and day of week number is 7.

Correct Average daily count try =
VAR NumberOfTransactions =
    CALCULATE (
        COUNT ( 'Ticketing fact table exports'[ Ticket Number] ),
        FILTER (
            'Ticketing fact table exports',
            RELATED ( Clock[Working Hour] ) <> 8
                || RELATED ( 'Date'[Day of Week Number] ) <> 7
        )
    )
VAR NumberOfDays =
    CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[DatesWithSales] = TRUE )
VAR Result =
    DIVIDE ( NumberOfTransactions, NumberOfDays )
RETURN
    Result

Additionally, if you are able to do some transformations in Power Query Editor, you can filter out the rows in advance to avoid including these error data in the model. For example, I'd like to remove the data in the red rectangle.

Then select a Date column -> Date Filters -> Custom Filter to open the Filter Rows dialogue window. Select Advanced, add the clauses with Day of week number does not equal 7 Or Working Hour does not equal 8. Pay attention to use Or, it is the same logic as described above for "||".

After that, you will get the rows removed from the data model.

M code for this step:

#"Filtered Rows" = Table.SelectRows(#"Changed Type",each [Day of week number]<>7 or [Working Hour]<>8)

 Hope this helps.

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[MFelix · Accepted solution]

Hi @Ginjko ,

How is your model setup? Can you please share a sample file or mockup data?

Based on the information you are providing believe that your issue in on the way you are calculating the filtering.

Try the following:

Correct Average daily count try =
VAR NumberOfTransactions =
    CALCULATE (
        COUNT ( 'Ticketing fact table exports'[ Ticket Number] ),
        Clock[Working Hour] <> 8
            && 'Date'[Day of Week Number] <> 7
    )
VAR NumberOfDays =
    CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[DatesWithSales] = TRUE )
VAR Result =
    DIVIDE ( NumberOfTransactions, NumberOfDays )
RETURN
    Result

this is just an atempt without data is difficult to give you the correct result.

[Ginjko]

Thanks a lot for your time i replaced the && By || and it works, I need to find good infos on these logical operator as realise I don't fully understand them.