Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Incorrect row values

Hi, I have a matrix that is displaying the row values incorrectly. The matrix shows inaccurate row data but an accurate distinct grand total. If I have a meter that belongs to more than one segment , it's billing date is getting counted in both their total's if the meter had been billed in one segment for a specific date then re-billed in another segment later on in the year for the same date. My data table is below:

Meter     Segment             Billing Start Date
1             Direct                 01/11/2018
1              Fixed                  01/10/2018 
1              Flexible             01/10/2018
1             Flexible               01/07/2018
1             Indirect              01/10/2018
1              Indirect            01/08/2018
1             Ultra                01/10/2018

 

This gives the below matrix output: 

 

Segment    Total Billed Months

Direct                      1

Fixed                       1

Flexible                   2

Indirect                   2

Ultra                       1

Total                      4

 

Essentially, the column total is correct as there are 4 distinct start dates. However, when this is by row level the start date for one meter can belong to different segments and is counting it more than once. For instance, in the table 01/10/2018 is counted many times but I only want to count it once/ the first occurence of the date - ignoring the other duplicates

5 REPLIES 5
AntrikshSharma
Community Champion
Community Champion

@Anonymous  for 1/10 how do you establish a relation with Segment, how to know which segment should be considered the first one for this date?

 

Since 1/10 appears in Fixed, Flexible, and Indirect, which one amongst these 3 needs to considered?

Anonymous
Not applicable

Hi Antriksh, Thanks for your reply. In this case, ideally I would like to split the count across each segment. So since 1/10 belongs to 3 segments it would count 0.33 across Fixed, Flexible, and Indirect. If that's not possible, ideally which ever is the last segment that the date falls into would be best to count Thanks

@Anonymous Not sure if this is the desired result, but I have tried, see if this works. The file is attached below my signature:

11.png

Total Billed Month = 
IF ( 
    ISINSCOPE ( ph[Segment] ),
    VAR CurrentSegment =
        SELECTEDVALUE ( ph[Segment] )
    VAR DatesInCurrentSegment =
        CALCULATETABLE (
            VALUES ( ph[Billing Start Date] ),
            ph[Segment] = CurrentSegment,
            REMOVEFILTERS ( ph )
        )
    VAR SegmentsWithDatesInCurrentSegment =
        // Gets the segments that have same dates as in the current segment
        CALCULATETABLE (
            ph,
            ph[Billing Start Date] IN DatesInCurrentSegment,
            REMOVEFILTERS ( ph )
        )
    VAR RankToFindCommonDate =
        // Ranking is being done to identify the occurence of dates amongst segments
        ADDCOLUMNS (
            SegmentsWithDatesInCurrentSegment,
            "@Rank", RANKX ( SegmentsWithDatesInCurrentSegment, ph[Billing Start Date] )
        )
    VAR FilterCommonDate =
        // This variable stores the dates that are duplicates amongst segments
        FILTER ( RankToFindCommonDate, [@Rank] = 1 )
    VAR Result =
        // This variable removes the duplicates
        EXCEPT ( RankToFindCommonDate, FilterCommonDate )
    RETURN
        COUNTROWS ( Result ) + DIVIDE ( 1, COUNTROWS ( FilterCommonDate ) ),
    DISTINCTCOUNT ( ph[Billing Start Date] )
)

 

Anonymous
Not applicable

Thanks Antriksh. Thart is how i'd like it to be split. It seems to have worked in my sample file. However, when I have created the measure in my full dataset (which has sensitive info hence the sample table) I still seem to get the original output.

 

Segment                Total Billed Months            Forum DAX

Direct                           1                                         1

Fixed                             1                                        1

Flexible                          2                                       2

Indirect                         2                                         2

Ultra                              1                                        1

 Total                            4                                        4

 

Forum Dax is the measure from your post. Total Billed Months = CALCULATE(SUMX(DISITNCT(meter(DISTINCTCOUNT(billing start date)) Thanks

@Anonymous Without PBIX file I can't provide much support, there can be a lot of factors that are hindering the calculation.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors