DAX code

[Anonymous]

Hi all,

I'm quite new with PowerBI and DAX and I'm struggling atm with a DAX query (it's quite long).

I have a Live Connection and created a table where I can see all the data. 

I need to write a simple DAX code as all I need is filter 'Cust Account' [ Acount Number] = 026020063405 and give me all the below columns in the table.

Below is only a part of the DAX code as the forum doesn't allow me to attach the whole code

DAX code:

// DAX Query
DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"026020063405"}, 'Cust Account'[Account Number])

  VAR __SQDS0Core = 
    SUMMARIZECOLUMNS(
      'Cust Account'[Account Name],
      __DS0FilterTable,
      "MinAccount_Name", CALCULATE(MIN('Cust Account'[Account Name]))
    )

  VAR __SQDS0BodyLimited = 
    TOPN(150000, __SQDS0Core, [MinAccount_Name], 0)

  VAR __DS0Core = 
    SELECTCOLUMNS(
      KEEPFILTERS(
        FILTER(
          KEEPFILTERS(
            SUMMARIZECOLUMNS(
              'OpCo'[OpCo],
              'Cust Account'[Account Number],
              'Cust Account'[RU Number],
              'Cust Account'[Account Name],
              'Install Base'[IB Customer],
              'Install Base'[IB Address2],
              'Install Base'[IB City],
              'Install Base'[IB Postal Code],
              'Contract Sublines'[Contract Number],
              'Contract Sublines'[Start Date],
              'Contract Sublines'[End Date],
              'Contract Header'[Customer PO Number],
              'Contract Header'[Price Increase Code],
              'Contract Header'[Contract Structure],
              'Contract Header'[Payment Term name],
              'Contract Sublines'[Invoice Grouping],
              'Contract Sublines'[Serial Number],
              'Item Master'[Model Name],
              'Contract Sublines'[Last Reading],
              'Contract Sublines'[Last Reading Source],
              'Contract Sublines'[Subline Status],
              'Contract Sublines'[Counter],
              'Contract Sublines'[Service Colour],
              'Contract Sublines'[Line Type],
              'Contract Sublines'[Frequency],
              'Contract Sublines'[Inv Rule],
              'Contract Sublines'[Period Amount],
              'Service Contract Price Breaks'[Price],
              'Contract Sublines'[Line Number],
              __DS0FilterTable,
              __SQDS0BodyLimited,
              "CountRowsContract_Sublines", CALCULATE(COUNTROWS('Contract Sublines')),
              "CountRowsSublines", CALCULATE(COUNTROWS('Sublines')),
              "CountRowsService_Contract_Price_Breaks", CALCULATE(COUNTROWS('Service Contract Price Breaks'))
            )
          ),
          OR(
            OR(
              OR(
                OR(
                  OR(
                    OR(
                      OR(
                        OR(
                          OR(
                            OR(
                              OR(
                                OR(
                                  OR(
                                    OR(
                                      OR(
                                        OR(
                                          OR(
                                            OR(
                                              OR(
                                                OR(
                                                  OR(
                                                    OR(
                                                      OR(
                                                        OR(
                                                          OR(
                                                            OR(
                                                              OR(
                                                                OR(
                                                                  NOT(ISBLANK('OpCo'[OpCo])),
                                                                  NOT(ISBLANK('Cust Account'[Account Number]))
                                                                ),
                                                                NOT(ISBLANK('Cust Account'[RU Number]))
                                                              ),
                                                              NOT(ISBLANK('Cust Account'[Account Name]))
                                                            ),
                                                            NOT(ISBLANK('Install Base'[IB Customer]))
                                                          ),
                                                          NOT(ISBLANK('Install Base'[IB Address2]))
                                                        ),
                                                        NOT(ISBLANK('Install Base'[IB City]))
                                                      ),
                                                      NOT(ISBLANK('Install Base'[IB Postal Code]))
                                                    ),
                                                    NOT(ISBLANK('Contract Sublines'[Contract Number]))
                                                  ),
                                                  NOT(ISBLANK('Contract Sublines'[Start Date]))
                                                ),
                                                NOT(ISBLANK('Contract Sublines'[End Date]))
                                              ),
                                              NOT(ISBLANK('Contract Header'[Customer PO Number]))
                                            ),
                                            NOT(ISBLANK('Contract Header'[Price Increase Code]))
                                          ),
                                          NOT(ISBLANK('Contract Header'[Contract Structure]))
                                        ),
                                        NOT(ISBLANK('Contract Header'[Payment Term name]))
                                      ),
                                      NOT(ISBLANK('Contract Sublines'[Invoice Grouping]))
                                    ),
                                    NOT(ISBLANK('Contract Sublines'[Serial Number]))
                                  ),
                                  NOT(ISBLANK('Item Master'[Model Name]))
                                ),
                                NOT(ISBLANK('Contract Sublines'[Last Reading]))
                              ),
                              NOT(ISBLANK('Contract Sublines'[Last Reading Source]))
                            ),
                            NOT(ISBLANK('Contract Sublines'[Subline Status]))
                          ),
                          NOT(ISBLANK('Contract Sublines'[Counter]))
                        ),
                        NOT(ISBLANK('Contract Sublines'[Service Colour]))
                      ),
                      NOT(ISBLANK('Contract Sublines'[Line Type]))
                    ),
                    NOT(ISBLANK('Contract Sublines'[Frequency]))
                  ),
                  NOT(ISBLANK('Contract Sublines'[Inv Rule]))
                ),
                NOT(ISBLANK('Contract Sublines'[Period Amount]))
              ),
              NOT(ISBLANK('Service Contract Price Breaks'[Price]))
            ),
            NOT(ISBLANK('Contract Sublines'[Line Number]))
          )
        )
      ),
      "'OpCo'[OpCo]", 'OpCo'[OpCo],
      "'Cust Account'[Account Number]", 'Cust Account'[Account Number],
      "'Cust Account'[RU Number]", 'Cust Account'[RU Number],
      "'Cust Account'[Account Name]", 'Cust Account'[Account Name],
      "'Install Base'[IB Customer]", 'Install Base'[IB Customer],
      "'Install Base'[IB Address2]", 'Install Base'[IB Address2],
      "'Install Base'[IB City]", 'Install Base'[IB City],
      "'Install Base'[IB Postal Code]", 'Install Base'[IB Postal Code],
      "'Contract Sublines'[Contract Number]", 'Contract Sublines'[Contract Number],
      "'Contract Sublines'[Start Date]", 'Contract Sublines'[Start Date],
      "'Contract Sublines'[End Date]", 'Contract Sublines'[End Date],
      "'Contract Header'[Customer PO Number]", 'Contract Header'[Customer PO Number],
      "'Contract Header'[Price Increase Code]", 'Contract Header'[Price Increase Code],
      "'Contract Header'[Contract Structure]", 'Contract Header'[Contract Structure],
      "'Contract Header'[Payment Term name]", 'Contract Header'[Payment Term name],
      "'Contract Sublines'[Invoice Grouping]", 'Contract Sublines'[Invoice Grouping],
      "'Contract Sublines'[Serial Number]", 'Contract Sublines'[Serial Number],
      "'Item Master'[Model Name]", 'Item Master'[Model Name],
      "'Contract Sublines'[Last Reading]", 'Contract Sublines'[Last Reading],
      "'Contract Sublines'[Last Reading Source]", 'Contract Sublines'[Last Reading Source],
      "'Contract Sublines'[Subline Status]", 'Contract Sublines'[Subline Status],
      "'Contract Sublines'[Counter]", 'Contract Sublines'[Counter],
      "'Contract Sublines'[Service Colour]", 'Contract Sublines'[Service Colour],
      "'Contract Sublines'[Line Type]", 'Contract Sublines'[Line Type],
      "'Contract Sublines'[Frequency]", 'Contract Sublines'[Frequency],
      "'Contract Sublines'[Inv Rule]", 'Contract Sublines'[Inv Rule],
      "'Contract Sublines'[Period Amount]", 'Contract Sublines'[Period Amount],
      "'Service Contract Price Breaks'[Price]", 'Service Contract Price Breaks'[Price],
      "'Contract Sublines'[Line Number]", 'Contract Sublines'[Line Number]
    )

[v-xiaotang]

Hi @GeorgeGabaroi 

If you want to filter the table at visual level, you can create a measure,

FilterMeasure = If(Min('Cust Account' [ Acount Number]) = 026020063405,0,1)

then put it into visual-level filter, and set 1, then it will filter 026020063405 out.

If you want to filter it out when calculating, for example, you want to sum total of those values where  'Cust Account' [ Acount Number] = 026020063405, use this measure,

sum= calculate(sum('Cust Account'[value]),filter(all('Cust Account'), 'Cust Account'[Acount Number] <> 026020063405))

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[lbendlin]

Please describe the business problem you are trying to solve. Provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.