Forum Discussion

RanjanThammaiah's avatar
RanjanThammaiah
Icon for Helper V rankHelper V
6 years ago

Is there any possibility to filter the data without table relationship

Hi All,
 

can someone help me to filter the data without having a table relationship?

I have data where I shouldn't create a relationship to arrive at the cross-selling numbers. but I need the Total value should be appearing for the intersecting Count.

Currently, the total on the tooltip is showing the Total value of the Column.

 

I have used the below DAX- which did not work.

 

Measure = CALCULATE([TVPS],
FILTER(ALL('S&P'),
'S&P'[SubServiceLineGroupingDesc]in VALUES('Comparision SSL'[SSL])))
 
attached the sample Report

 

Basically the tooltip should show the below value.

Help would be much appreciated.

 

Regards,

Ranjan

Data Protection
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

    • RanjanThammaiah's avatar
      RanjanThammaiah
      Icon for Helper V rankHelper V
      6 years ago

      Hi v-juanli-msft  sorry that is total Value i waas expecting for the "11"  Count in my table tooltip(14,471,559 ).

       

      Its basically the intersecting value for Both Audit and Adv solution.

       

      I have worked out this in Excel by taking the combination of UDN(ID) count with total value.

      But currently the tooltip i am getting the total value of Audit/Adv Solution i.e 

      Adv Solutions           Audit
      21,511,194             51,910,380

       

      Kindly let me know if you need more clarification on this.

       

      Regards,

      Ranjan 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    I didn't try it in your report, but you can pass filters without using a relatonship with the TREATAS() function.  Here is an example using your measure

     

    Measure = CALCULATE([TVPS],
    TREATAS(VALUES('Comparision SSL'[SSL]),
    'S&P'[SubServiceLineGroupingDesc]))
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • RanjanThammaiah's avatar
      RanjanThammaiah
      Icon for Helper V rankHelper V
      6 years ago

      Hi mahoneypat  sorry, My bad luck. it doesn't give any value(Just (Blank).

       

      Basically I have created the table using the below formula.

       

      Cross Selling =
      VAR InitialPurchase =VALUES('S&P'[UDN])
      VAR ComparisionPurchase =CALCULATETABLE(VALUES('S&P'[UDN]),
      ALLSELECTED(SSL),
      TREATAS(VALUES('Comparision SSL'[SSL]),'S&P'[SubServiceLineGroupingDesc]))
      Return
      IF(SELECTEDVALUE(SSL[SSL])=SELECTEDVALUE('Comparision SSL'[SSL- Copy]),
      BLANK(),
      COUNTROWS(INTERSECT( InitialPurchase, ComparisionPurchase)))

       

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi RanjanThammaiah 

    I'm not clear about "11 count" you refered.

    When i change measure, the tooltip show different result.

    Measure =
CALCULATE (
    [TVPS],
    FILTER (
        'S&P',
        FIND ( 'S&P'[SubServiceLineGroupingDesc], MAX ( 'Comparision SSL'[SSL] ), 1, 0 ) > 0
    )
)

    is this what you want?

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.