Forum Discussion

dzabba00's avatar
dzabba00
New Member
7 years ago

Create non-numeric measure from dimension attribute

 

Hello Dax Masters!

I have two tables in my model, first is standard fact table and the second one is dimension (relation: many from fact - one dimension) relation key:[key: shop_id,prod_id,yyyymm]

 

Sales:

 

Shop_Id, Prod_id,  Date,        AmountSold
1        22        2018-03-01   100
1        22        2018-04-01   100
1        22        2018-05-01   Yes
1        22        2018-06-01   100
1        22        2018-07-01   100
1        22        2018-08-01   100
1        22        2018-09-01   100
1        22        2018-10-01   100
1        22        2018-11-01   100
1        22        2018-12-01   100
1        22        2019-03-01   100
1        22        2019-04-01   100
1        11        2018-01-01   100
1        11        2018-02-01   100
1        11        2018-03-01   100
1        11        2018-06-01   100
1        11        2018-07-01   100
1        11        2018-08-01   100
1        11        2018-09-01   100
1        11        2018-10-01   100
1        11        2018-11-01   100
1        11        2018-12-01   100
1        11        2019-01-01   100
1        11        2019-02-01   100
1        11        2019-03-01   100
1        11        2019-04-01   100

 

Product Status:

 

Shop_id, Prod_id,  Date,    CanISellit
1        22        2018-01   No
1        22        2018-02   No
1        22        2018-03   Yes
1        22        2018-04   Yes
1        22        2018-05   Yes
1        22        2018-06   Yes
1        22        2018-07   Yes
1        22        2018-08   Yes
1        22        2018-09   Yes
1        22        2018-10   Yes
1        22        2018-11   Yes
1        22        2018-12   Yes
1        22        2019-01   No
1        22        2019-02   No
1        22        2019-03   Yes
1        22        2019-04   Yes
1        11        2018-01   Yes
1        11        2018-02   Yes
1        11        2018-03   Yes
1        11        2018-04   Yes
1        11        2018-05   Yes
1        11        2018-06   Yes
1        11        2018-07   Yes
1        11        2018-08   Yes
1        11        2018-09   Yes
1        11        2018-10   Yes
1        11        2018-11   Yes
1        11        2018-12   Yes
1        11        2019-01   No
1        11        2019-02   No
1        11        2019-03   No
1        11        2019-04   No

 

My goal is to achive result based amountsales and 'CanISellit'.

Desired goal is to show in matrix values of sales with 'CanIsellit' attribute when there is no 'Yes' value in canisellit

And i want to show:

1. Products that are allowed for sell: CaniSellit = YES and SalesAmount

2.Procuts that are not allowed for sell: CanIsellit= No but got salesamount

3.Product that are not allowed for sell: CanIsellit = No and dont  have salesamount 

 

Expected result:

  DateYYYYMM          
  2019-01 2019-022019-032019-042019-052019-06
Shop_IDProd idCanISellitSales AmountCanISellitSales AmountCanISellitSales AmountCanISellitSales AmountCanISellitSales AmountCanISellitSales Amount
122No0No0Yes300Yes200Yes500No600

 

I was trying to create non-numeric measure for 'CanIsellit' and display in matrix but i dont know how (was trying to use values,switch,salesmeasure with blanks etc.) but it didnt work ;/ 

"Show data with no value" will not work both in excel pp and powerbi ;/ 

 

 

 

 

 

 

Need Help

1 Reply

  • dax's avatar
    dax
    Community Support
    7 years ago

    Hi dzabba00,

     

    According to your description and image, I am not clear about your logic, could you please  explain this in details?

    1.Products that are allowed for sell: CaniSellit = YES and SalesAmount
    2.Procuts that are not allowed for sell: CanIsellit= No but got salesamount

    3.Product that are not allowed for sell: CanIsellit = No and dont  have salesamount 

    Did you mean when CaniSellit = “yes”, show saleamount in  Matrix, when CaniSellit = “No” but have saleamount, show 0, when CaniSellit = “No” and no saleamount , show blank in Matrix?

    By the way, Why the corresponding “Sales Amount” for “2019-03” is displayed as 300, while the actual value in “Sales” table is 100? Do you want running total? And there is no 2019-05 and 2019-06 in sample  table, how to get the displayed sales amount for them in Matrix?

    Best Regards,
    Zoe Zhi

     

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