Forum Discussion

sboinala's avatar
sboinala
Regular Visitor
6 years ago
Solved

Count values

I have two tables Customers a b c d e f   Account Type: Customer Fuel a Gas a Elec b Gas c Gas c Elec d Gas d Elec e Elec e Elec ...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try these three measures first:

    Gas & Elec Counts = 
SUMX (
    DISTINCT ( Customer[Customers] ),
    CALCULATE (
        IF (
            CALCULATE (
                DISTINCTCOUNT ( 'Account Type'[Customer&Fuel] ),
                FILTER (
                    ALLSELECTED ( 'Account Type' ),
                    'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                )
            ) > 1,
            1,
            0
        )
    )
)
    Gas Only Count = 
SUMX (
    DISTINCT ( Customer[Customers] ),
    CALCULATE (
        IF (
            CALCULATE (
                DISTINCTCOUNT ( 'Account Type'[Customer&Fuel] ),
                FILTER (
                    ALLSELECTED ( 'Account Type' ),
                    'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                )
            ) = 1
                && CALCULATE (
                    MAX ( 'Account Type'[Fuel] ),
                    FILTER (
                        ALLSELECTED ( 'Account Type' ),
                        'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                    )
                ) = "Gas",
            1,
            0
        )
    )
)
    Elec Only Count = 
SUMX (
    DISTINCT ( Customer[Customers] ),
    CALCULATE (
        IF (
            CALCULATE (
                DISTINCTCOUNT ( 'Account Type'[Customer&Fuel] ),
                FILTER (
                    ALLSELECTED ( 'Account Type' ),
                    'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                )
            ) = 1
                && CALCULATE (
                    MAX ( 'Account Type'[Fuel] ),
                    FILTER (
                        ALLSELECTED ( 'Account Type' ),
                        'Account Type'[Customer] IN FILTERS ( Customer[Customers] )
                    )
                ) = "Elec",
            1,
            0
        )
    )
)

    Then create a slicer table by Enter Data:

    Then try this count measure:

    Count =
SUMX (
    DISTINCT ( 'Slicer Table'[Category] ),
    CALCULATE (
        SWITCH (
            MAX ( 'Slicer Table'[Category] ),
            "Elec Counts", [Elec Only Count],
            "Gas  Counts", [Gas Only Count],
            "Gas & Elec Counts", [Gas & Elec Counts]
        )
    )
)

    The result shows:

    Here is my test pbix file:

    pbix 

    Hope this helps.

     

    Best Regards,

    Giotto Zhi

az38's avatar
az38
Community Champion
6 years ago

Hi sboinala 

I see 2 required outputs in your post? what do you need exactly?

for first matrix just creatte a visual matrix, drop Customer to rows field, Fuel into Columns, Customer to values and set Values aggregation parameter as Count (Distinct)

 

for second, fuel - as rows, customer as Values and the same set Values aggregation parameter as Count (Distinct)

  • sboinala's avatar
    sboinala
    Regular Visitor
    6 years ago

    The output i am looking for is :

    Gas & Elec3
    Gas2
    Elec1

     

    I have tried the below ,however i couldnt get the first value  Gas & Elec

    • az38's avatar
      az38
      Community Champion
      6 years ago

      sboinala 

      create a column

      FuelType = 
var _isGas = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Customer]),'Table'[Fuel]="Gas")
var _isElec = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Customer]),'Table'[Fuel]="Elec")
RETURN
SWITCH(TRUE(),
_isElec > 0 && _isGas > 0, "Gas & Elec",
_isElec > 0, "Elec Only",
_isGas > 0, "Gas Only"
)

      then add new column FuelType as rows and Count(Distinct) of Customers as Value