Trying to achieve listed & not listed count

[vm8181]

I have data model where I have two tables Dim_Product and Fact_sales with one to many relationship on the basis of productCode. 

I am trying to achieve listed & not listed(where sales are available listed else not listed). It's showing fine for platform wise, but not giving appropriate values for city wise.
SKU_Code or product name is the attribute which is common across platform.

Dim_Product Table

ProductCode	SKUCode	ProductName	Category	Platform
P001_AMZ	SKU001	Smart Watch	Electronics	Amazon
P001_FLK	SKU001	Smart Watch	Electronics	FK
P002_AMZ	SKU002	Wireless Earbuds	Electronics	Amazon
P002_FLK	SKU002	Wireless Earbuds	Electronics	FK
P003_AMZ	SKU003	Yoga Mat	Sports	Amazon
P003_FLK	SKU003	Yoga Mat	Sports	FK
P004_AMZ	SKU004	Running Shoes	Sports	Amazon
P004_FLK	SKU004	Running Shoes	Sports	FK
P005_AMZ	SKU005	Vacuum Cleaner	Home Appliances	Amazon
P005_FLK	SKU005	Vacuum Cleaner	Home Appliances	FK
P006_AMZ	SKU006	Blender	Home Appliances	Amazon
P006_FLK	SKU006	Blender	Home Appliances	FK

Fact_Sales Table

Platform	ProductCode	City	Sales
FK	P001_FLK	Los Angeles	1400
Amazon	P002_AMZ	Chicago	2000
FK	P002_FLK	Houston	1900
FK	P003_FLK	San Francisco	1300
Amazon	P004_AMZ	Seattle	1700
Amazon	P005_AMZ	Austin	2500
FK	P005_FLK	Boston	2400
Amazon	P006_AMZ	Denver	1100
Amazon	P001_AMZ	Dallas	1550
FK	P001_FLK	Detroit	1450
Amazon	P002_AMZ	Phoenix	2100

Measure

Sales = SUM(Sales)
Status = SWITCH(TRUE(), ISBLANK([_sales]), "Not Listed", "Listed")

Listed =

CALCULATE(

    COUNTROWS(Dim_Master),

    FILTER(

        Dim_Master,

        [status] = "Listed"

    )

)

Note Listed =

CALCULATE(

    COUNTROWS(Dim_Master),

    FILTER(

        Dim_Master,

        [status] = "Not Listed"

    )

)

I have attached a screenshot for the reference.

Any help would be greatful. Thank you so much.

[vm8181]

Hi @Ashish_Mathur , @Selva-Salimi , @Anonymous Thanks for your responses.
The problem has solved. 

Let me clear my explanation for better understanding.

Scenario:
-I have two tables: dim_product and fact_sales. My goal is to show listed and not listed products for different platforms and cities, considering whether sales are available or not.

-Platforms exist in dim_product, and there is a direct relationship(1 - to - many) between dim_product and fact_sales based on product code. So, calculating listed and not listed products for platforms works correctly.

-However, cities are only present in fact_sales. If I use cities from fact_sales, I will only see values where sales exist. Instead, I need a solution where cities with no sales also appear with a count of 0.

Solution Implemented:
To address this, I created a dim_city table in Power Query by referencing fact_sales and then designed measures accordingly.

Listed Products (for both Platforms and Cities)
Listed = CALCULATE(COUNT(Dim_Master[SKUCode]),FILTER(Fact_Sales, [_sales] <> 0))
This works correctly for both platforms and cities.

Not Listed Products (Separate Measures for Platforms & Cities)

For Platforms:
Not Listed Platform =
VAR status_ = SUMMARIZE(Dim_Master,Dim_Master[SKUCode],Dim_Master[ProductName],Dim_Master[Platform],
"sales", CALCULATE(SUM(Fact_Sales[Sales]) + 0))
VAR result = COUNTX(FILTER(status_, [sales] = 0),Dim_Master[SKUCode])
RETURN
result

For Cities:
Not Listed City =
VAR status_ = ADDCOLUMNS(CROSSJOIN(SUMMARIZE(Dim_Master, Dim_Master[SKUCode],Dim_Master[ProductName]), Cities),"sales", [_sales] + 0)
VAR result = COUNTX(FILTER(status_, [sales] = 0),Dim_Master[SKUCode])
RETURN
result

It's working correctly and filtering the values as expected.

[Anonymous]

Hi All,
Firstly @Ashish_Mathur  and @Selva-Salimi  thank you for yours solution!
And @vm8181 ,It looks like your problem is that the measure doesn't show up correctly when you use the city column, right?

In our test environment, we tried to use your code and there seems to be no problem, it is possible that this problem occurs due to context filtering being affected, if you can provide example data or a pbix file, or make further modifications in the pbix file I uploaded to reproduce your problem, this will better help you solve the problem, I look forward to hearing from you!

[Ashish_Mathur]

Hi,

Based on the 2 tables that you have shared, show the expected result.

[Selva-Salimi]

Hi @vm8181 

what is Dim_master table and what is the relation between this table and others?!