Forum Discussion

Pzin's avatar
Pzin
New Member
3 years ago
Solved

Help a starter - Counting Probelm

Hello Pros:),

 

i just started with PowerBI i have my fair share of Failure during the last month. There is one result i would like to achieve which just won't work...

I m gonna break it down to this:

Customer      Date                  Case 
A                 01.01.2023          1

A                 01.01.2023          1

B                 01.02.2023          1

B                 01.01.2023          1


What i want to do is, i wanna know how many days we were at a customer. (Per Customer and in Total)
We visited A twice at one day = 1 Day. 
We vistited B twice but on different days = 2 Days

That's working fine with a DistinctCount function for Date. BUT if i want to have the Total of all days at all customer i will get 2 (01.01.2023 and 01.02.2023) as a result. What i want to have is 3. 

Hope someone will help me with this little WarmUp:)

Greez
Pascale

Need Help
  • ahmedoye's avatar
    ahmedoye
    3 years ago
    Try the approach below. If it works for you, kindly mark this as a solution so anyone with similar issues can easily find it.

    Measure =
    SUMX(
             ALL(CustomersData[Customer]),
                    CALCULATE(
                                        DISTINCTCOUNT(CustomersData[Date])
                                     )
                                     )

3 Replies

  • ahmedoye's avatar
    ahmedoye
    Icon for Responsive Resident rankResponsive Resident
    3 years ago
    Try the approach below. If it works for you, kindly mark this as a solution so anyone with similar issues can easily find it.

    Measure =
    SUMX(
             ALL(CustomersData[Customer]),
                    CALCULATE(
                                        DISTINCTCOUNT(CustomersData[Date])
                                     )
                                     )
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Pzin 

     

    You can try the following methods.

    Measure =
VAR _table =
    SUMMARIZE ('Table', 'Table'[Customer],
        "Count",
            CALCULATE (
                DISTINCTCOUNT ( 'Table'[Date] ),
                ALLEXCEPT ( 'Table', 'Table'[Customer] )
            )
    )
RETURN
    SUMX ( _table, [Count] )

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Write these measures

    Visit days_Inter = DISTINCTCOUNT(Data[Date])
    Visit days = SUMX(VALUES(Data[Customer]),[Visit days_Inter])

    Hope this helps.