How get the Count of Unique recors of one field on the bases of Other uniue field.

[susheeltyagi]

HI,

I need the expert help. I am trying to find the formula for count of Unique record of one field (Firmware) on the bases of different field (Serial number).

I was using the below formula but the number of Firmware its not uniuqe :

FirmwareCount = CALCULATE(COUNT(DeviceQuery[Firmware]))

Example  :

DeviceID              Firmware

Device 1              ABC

Device 2              ABC

Device 3              ABC

Device 4              ZXCD

Device 5              ZXCD

Device 6              NMS

Expected Output :

Firmware      Count

   ABC                   3

   ZXCD                 2

   NMS                  1

My device table as below :

Table   : DeviceQuery

Expected Output :

  Expected Output should have Unique Number of Firmware from all the devices

Thanks

Susheel

[Tahreem24 · Accepted solution]

@susheeltyagi  Try this measure:

FirmwareCount = CALCULATE(DISTINCTCOUNT(DeviceQuery[Firmware]),ALLEXCEPT(DeviceQuery[SerialNumber]))

[susheeltyagi]

I got solve as by Creating new table as:
UniqueDeviceTable = ALL ( DeviceQuery[SerialNumber],DeviceQuery[Firmware] )

Create New Measure

FW Count = COUNTROWS (UniqueDeviceTable)

[amitchandak]

@susheeltyagi , Try a measure like

Distinctcount( DeviceQuery[SerialNumber])

[susheeltyagi]

Dear Friend,

Thanks for the idea

I have created the new table from the main Device Table

UniqueDeviceTable = ALL ( DeviceQuery[SerialNumber] , DeviceQuery[Firmware] )

Then I create the new Measure in it as below :

UniqueFW = Distinctcount( UniqueDeviceTable[Firmware])

also try with
FwCount = CALCULATE(DISTINCTCOUNT(UniqueDeviceTable[Firmware]),ALLEXCEPT(UniqueDeviceTable, UniqueDeviceTable[SerialNumber]))

In both the case I am getting the wrng Firmware count

The Expected Output should be as below:

Please help me for the distinct FW count

[susheeltyagi]

But I need the count of unique  Firmware on the bases of Unique SerialNumber.

How to implement in one formula ?

[Anonymous]

concatenate these two columns in a calculated columns and the use a countdistinct of this column as a measure.

Hope it helps