Forum Discussion

databubble's avatar
databubble
Frequent Visitor
9 years ago

Boolean measure breaks relationship?

Hi

 

I have tables DimSalesperson and FactSales. They are related by SalesPerson

 

DimSalesperson:

SalespersonTargetTotalSales
Amit4000
Barbara5000
Carolina6000
Deng7000

 

FactSales:

InvoiceNumSalesDateSalesPersonGrossInvoiceSalesTax
123/3/2016Deng758.8592
229/3/2016Barbara154.9398.97
35/4/2016Barbara651.5662.97
414/4/2016Deng262.223.63
524/4/2016Amit674.1794.68
630/4/2016Barbara468.9326.42
78/5/2016Carolina256.989.74
817/5/2016Deng939.9797.21
…etc...…etc...…etc...…etc...…etc...

 

I added three measures to FactSales:

 

  • NetAmt = sum('Fact'[GrossInvoice]) - sum('Fact'[SalesTax])
  • DiffToSalesTarget = [NetAmt]-min('Fact'[SalesTarget])
  • MetSalesTarget = [NetAmt] > min('Fact'[SalesTarget])

 

And I have a basic table visualistion showing total netAmt for each SalesPerson. (I have included Salesperson twice - once from Dimension and once from Fact - to clarify the later problem)

 

NetAmtSalesPersonSalesperson
4173.67AmitAmit
5905.81BarbaraBarbara
2249CarolinaCarolina
4262DengDeng

 

 

I can add TargetTotalSales and DiffToSalesTarget and it behaves as expected - the values are calculated in context.

 

NetAmtSalesPersonSalespersonTargetTotalSalesDiffToSalesTarget
4173.67AmitAmit4000173.67
5905.81BarbaraBarbara5000905.81
2249CarolinaCarolina6000-3751
4262DengDeng7000-2738

 

However if I add MetSalesTarget, for each sales person in the Fact table, I get a line for every SalesPerson in the Dimension table. It is as if the relationship no longer exists.:

 

NetAmtSalesPersonSalespersonTargetTotalSalesDiffToSalesTarggetMetSalesTarget
4173.67AmitAmit4000173.67TRUE
 AmitBarbara5000 FALSE
 AmitCarolina6000 FALSE
 AmitDeng7000 FALSE
 BarbaraAmit4000 FALSE
5905.81BarbaraBarbara5000905.81TRUE
 BarbaraCarolina6000 FALSE
 BarbaraDeng7000 FALSE
 CarolinaAmit4000 FALSE
 CarolinaBarbara5000 FALSE
2249CarolinaCarolina6000-3751FALSE
 CarolinaDeng7000 FALSE
 DengAmit4000 FALSE
 DengBarbara5000 FALSE
 DengCarolina6000 FALSE
4262DengDeng7000-2738FALSE

 

What am I doing wrong? 

 

Many thanks

Liz

Need Help

3 Replies

    • databubble's avatar
      databubble
      Frequent Visitor
      9 years ago

      Hi

       

      Thank you for helping with my problem.

       

      I see a filter gives the results I want, but it feels like a workaround to a problem in my measure. I am new to Power BI and not sure what is good practice! Is it usual to generate and filter out the "wrong" results ?

       

      Liz

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        databubble,

        Generally, we don’t drag SalesPerson fields of both table to create a visual. We only drag SalesPerson field from either DimSalesperson table or FactSales table to visuals in Power BI, this way, expected result will return.

        Regards,